Intro to PL/pgSQL Debugging

So, imagine you’ve built a complex function or procedure. You can already see how awesome your database works, but suddenly — bam! — the data is off, queries are slow, and your boss starts freaking out. That’s when debugging steps in.

Debugging in PL/pgSQL is needed to:

• Find logic bugs, like when a function returns something totally unexpected.
• Figure out what’s up with weird input data. Because sometimes database users enter not just data, but... something totally bizarre!
• Fix performance issues. Because code you wrote in a rush can run like a turtle searching for Wi-Fi in the Sahara desert.

Seriously though, debugging isn’t just about finding and fixing bugs. It’s a way to make your code better, faster, more efficient, and easier to read.

Main Approaches to PL/pgSQL Debugging

Debugging in PL/pgSQL can be done in a few ways. Let’s break them down one by one.

1. Using PostgreSQL’s Built-in Tools

PostgreSQL gives you a few built-in ways to diagnose stuff, including logging functions (RAISE NOTICE and RAISE EXCEPTION), and analyzing query execution plans (EXPLAIN ANALYZE). These tools help you see what’s going on inside your function.

2. Logging with RAISE NOTICE

RAISE NOTICE is your buddy if you want to see what data is flowing through your function, where things go sideways, or just check variable values. Unlike RAISE EXCEPTION, it doesn’t stop the function from running. For example, you can print out a variable’s value at every step.

DO $$
DECLARE
    counter INT := 0;
BEGIN
    FOR counter IN 1..5 LOOP
        RAISE NOTICE 'Current counter value: %', counter;
    END LOOP;
END $$;

This code prints out the counter values from 1 to 5. Simple magic, but super useful for debugging!

3. Using Third-Party Tools

Debugging PL/pgSQL can also be done with tools like pgAdmin (with a GUI). It lets you set breakpoints and see variable values in real time. If you’re the kind of person who likes visual helpers, pgAdmin will be your best friend.

Debugging Steps

When you start debugging a function or procedure, it’s important to follow a certain sequence. Let’s go through each step in more detail:

1. Analyzing Input Data

The first thing to check is the input data. Make sure the data your function gets doesn’t have errors or weird values. For example, you can check all input parameters using RAISE NOTICE:

CREATE FUNCTION check_input(x INTEGER) RETURNS VOID AS $$
BEGIN
    IF x IS NULL THEN
        RAISE EXCEPTION 'Input value must not be NULL!';
    END IF;
    RAISE NOTICE 'Input value: %', x;
END;
$$ LANGUAGE plpgsql;

This example shows how to warn users about problems with input data.

2. Checking Each Step’s Execution

Break your function into logical blocks and add RAISE NOTICE at key points. This helps you see exactly where things go wrong.

CREATE FUNCTION calculate_discount(price NUMERIC, discount NUMERIC) RETURNS NUMERIC AS $$
BEGIN
    RAISE NOTICE 'Function start: price %, discount %', price, discount;

    IF price <= 0 THEN
        RAISE EXCEPTION 'Price can’t be negative or zero!';
    END IF;

    IF discount < 0 OR discount > 100 THEN
        RAISE EXCEPTION 'Discount must be between 0 and 100!';
    END IF;

    RETURN price - (price * discount / 100);
END;
$$ LANGUAGE plpgsql;

Here, at every debugging step, you get helpful messages about what’s going on.

3. Optimization and Fixing Issues
4. Once you’ve found the bug, fix it. If it’s a performance thing, use analysis tools like EXPLAIN ANALYZE to optimize your queries.

Debugging Skills in Practice

Let’s look at a real task: we have a function that adds a record to a table and returns the generated ID. Seems logical and simple, but sometimes the function fails, and we want to know why.

Original function:

CREATE FUNCTION add_student(name TEXT, age INTEGER) RETURNS INTEGER AS $$
DECLARE
    new_id INTEGER;
BEGIN
    INSERT INTO students (name, age) VALUES (name, age) RETURNING id INTO new_id;
    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

If you call this function with bad data, like age < 0, it throws an error. Let’s improve it with some debugging tools.

Improved function with logging:

CREATE FUNCTION add_student(name TEXT, age INTEGER) RETURNS INTEGER AS $$
DECLARE
    new_id INTEGER;
BEGIN
    -- Logging input data
    RAISE NOTICE 'Adding student: name %, age %', name, age;

    -- Checking age validity
    IF age < 0 THEN
        RAISE EXCEPTION 'Age can’t be negative!';
    END IF;

    -- Adding student and returning their ID
    INSERT INTO students (name, age) VALUES (name, age) RETURNING id INTO new_id;

    -- Logging successful execution
    RAISE NOTICE 'Student added with ID %', new_id;

    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

Now, if there’s an error, you’ll know exactly what went wrong, thanks to the messages printed with RAISE NOTICE.

Handy Tips Before You Go

1. Don’t forget to remove unnecessary logging in production code. RAISE NOTICE is awesome for debugging, but if you leave it everywhere, your production logs will get messy.
2. Work with small chunks of code. If your function is too complicated, break it into smaller parts. Debugging will be way easier.
3. Practice regularly. The more you write and debug code, the faster you’ll get at finding and fixing bugs.

Debugging is like playing detective, except instead of a magnifying glass, you’ve got SQL queries and PL/pgSQL logic. You get better at it with experience, but every bug you squash makes you just a little bit better!

2

Task

SQL SELF, level 55, lesson 0

Locked

Input Validation Using RAISE EXCEPTION and RAISE NOTICE

Input Validation Using RAISE EXCEPTION and RAISE NOTICE

Open