Error Handling: RAISE EXCEPTION

Imagine you're writing a function that calculates a student's average grade. What happens if you try to divide by zero (like, if there are no grades)? Try to ship that code to production—and an unexpected guest in the form of an error will show up right away. PL/pgSQL gives you powerful tools to handle these errors, making your code robust, safe, and actually fun to work with.

Error handling in PL/pgSQL lets you:

1. Generate messages explaining what went wrong.
2. Stop code execution in case of critical errors.
3. Log issues for later analysis.

Main PL/pgSQL Message Levels

PL/pgSQL supports several message levels that help devs efficiently diagnose and fix problems. Here they are:

• NOTICE: outputs an informational message. Used for debugging.
• WARNING: notifies about a potential issue that doesn't stop the program.
• EXCEPTION: a critical error that stops the program (and returns control to the calling code).

Message levels in PL/pgSQL

RAISE Command Syntax

To generate messages and handle errors, you use the RAISE statement. Here's the basic syntax:

RAISE <message level> 'message text' [, variables...];

• <message level> — NOTICE, WARNING, EXCEPTION.
• 'message text' — description of the problem.
• [variables...] — extra values you can pass into the message text.

Example 1: Using RAISE NOTICE

Sometimes it's important to know what's happening inside your function. Like, for debugging a loop:

DO $$
BEGIN
    FOR i IN 1..5 LOOP
        RAISE NOTICE 'Current value of i: %', i;
    END LOOP;
END
$$;

Result: Outputs to the console lines like Current value of i: 1, Current value of i: 2, and so on up to 5.

Example 2: Using RAISE EXCEPTION

Now imagine you're writing a function that should end with an error under certain conditions:

DO $$
BEGIN
    IF 1 = 1 THEN
        RAISE EXCEPTION 'Something went wrong!';
    END IF;
END
$$;

Result: execution stops, and the error message is shown in the console.

Working with Parameters in RAISE

By using parameters, you can personalize the message text. For this, use the % placeholder:

Example 3: Inserting Variables in RAISE

DO $$
DECLARE
    student_name TEXT := 'Ivan';
    average_score NUMERIC := NULL;
BEGIN
    IF average_score IS NULL THEN
        RAISE EXCEPTION 'Student % has no average grade!', student_name;
    END IF;
END
$$;

Result: message Student Ivan has no average grade!.

As you can see, % gets replaced by the student_name variable, making the message more meaningful.

Generating Custom Errors

Errors aren't just for emergencies! Sometimes you need to create them on purpose to protect your code from bad data.

Example 4: Checking Input Values

Let's write a function that checks the input number and throws an error if it's negative:

CREATE OR REPLACE FUNCTION check_positive(value NUMERIC)
RETURNS TEXT AS $$
BEGIN
    IF value < 0 THEN
        RAISE EXCEPTION 'Number % is negative!', value;
    END IF;
    RETURN 'Number is valid.';
END;
$$ LANGUAGE plpgsql;

Now let's test the function:

SELECT check_positive(-5);

Result: error message Number -5 is negative!.

If you pass a positive value:

SELECT check_positive(10);

Result: Number is valid.

Error Handling in Context

It's great if you can generate errors. But it's even better to handle them depending on the situation. For that, you use the BEGIN ... EXCEPTION block.

Error Handling Structure

BEGIN
    -- Your main code
EXCEPTION
    WHEN ERROR_TYPE THEN
        -- What to do in case of this error
    WHEN ANOTHER_ERROR THEN
        -- Actions for another error
    WHEN OTHERS THEN
        -- Handle all other errors
END;

Let's break down the components:

• EXCEPTION — keyword marking the start of the error handling block.
• WHEN — lets you specify the exact type of error to handle, like unique_violation or division_by_zero.
• OTHERS — used to handle all errors not explicitly listed in WHEN blocks.

Example 5: Handling Division by Zero

Let's show error handling with a simple division function:

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    -- Trying to do the division
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE WARNING 'Tried to divide by zero. Returning NULL.';
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Let's test the function:

SELECT safe_divide(10, 2);  -- Expected result: 5
SELECT safe_divide(10, 0);  -- Expected result: NULL and a warning in the console

Common Mistakes When Using RAISE

Missing message level. If you forget to specify the level, PostgreSQL will throw an error.

Wrong:

RAISE 'Message without level';

Right:

RAISE NOTICE 'Message with NOTICE level';

Wrong parameters. If you use %, make sure you pass the right number of variables.

Wrong:

RAISE NOTICE 'Example with parameter %';

Right:

RAISE NOTICE 'Example with parameter %', 'value';

Overdoing it. Using RAISE EXCEPTION too much can interrupt important operations. Use it wisely.

Pro Tips

1. Be careful with the WHEN OTHERS block. Whenever possible, specify exact errors so you don't catch stuff that should be handled differently.
2. Use RAISE for debugging. Never leave errors unhandled.
3. Don't forget about performance. Error handling can be expensive, especially in big procedures.

If you do everything right, your procedures will be tough and can survive even unexpected crashes. Your PM will be super proud of you!