PL/pgSQL Exception Propagation

Master exception propagation across nested blocks — local vs propagated handling, the bare RAISE; for reraising, GET STACKED DIAGNOSTICS for structured error inspection, and savepoint-based DML rollback at block boundaries. Critical correction: SQLERRM IS a valid identifier in PL/pgSQL (the original tutorial's claim that it was invalid was wrong).

When an exception is raised inside a block, PL/pgSQL looks for a matching handler. If the current block has one, the handler runs and execution continues past the block. If not, the exception propagates up to the enclosing block, then to its enclosing block, and so on, until either:

• A matching handler is found and runs, or
• The exception reaches the outermost level — at which point the transaction aborts and the error is returned to the client.

This makes nested BEGIN ... END blocks the natural place to manage exception scope. You catch what you know how to handle locally; let the rest float up to a handler that knows what to do with it.

DROP TABLE IF EXISTS customer;

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    name        TEXT
);

INSERT INTO customer (customer_id, name) VALUES
    (1, 'John Doe'),
    (2, 'Jane Smith'),
    (3, 'Alice Johnson'),
    (4, 'Mark Johnson'),
    (5, 'Emily Brown');

When the inner block has a handler that matches the raised exception, the outer block never sees it:

DO $$
BEGIN
    RAISE NOTICE 'Outer block: starting';

    BEGIN
        RAISE NOTICE 'Inner block: starting';
        RAISE division_by_zero;          -- raises in the inner block
        RAISE NOTICE 'Inner block: this never prints';
    EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE 'Inner block: caught the exception locally';
    END;

    RAISE NOTICE 'Outer block: continuing past the inner block';
END
$$;

Inner caught it. Outer continues normally — never knew anything went wrong.

If the inner block's handler doesn't match the raised exception, the exception propagates to the outer block. Here the inner block only handles no_data_found, but a division_by_zero is raised:

DO $$
BEGIN
    RAISE NOTICE 'Outer block: starting';

    BEGIN
        RAISE NOTICE 'Inner block: starting';
        RAISE division_by_zero;
    EXCEPTION
        WHEN no_data_found THEN
            -- doesn't match division_by_zero; ignored
            RAISE NOTICE 'Inner block: caught no_data_found (NOT this case)';
    END;

    RAISE NOTICE 'Outer block: this never prints';
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Outer block: caught propagated division_by_zero';
END
$$;

The inner handler had no matching WHEN, so propagation kicked in. The outer block's WHEN division_by_zero did match, and the outer handler ran. Note that the line after the inner block in the outer block didn't run — the outer block's normal flow was also interrupted.

Sometimes you want the inner block to log something locally, then let the exception keep going. A bare RAISE; in the handler reraises the current exception:

DO $$
BEGIN
    RAISE NOTICE 'Outer block: starting';

    BEGIN
        RAISE NOTICE 'Inner block: doing something risky';
        RAISE division_by_zero;
    EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE 'Inner block: logged the failure';
            RAISE;             -- bare RAISE: send the same exception up
    END;

    RAISE NOTICE 'Outer block: this never prints';
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Outer block: caught the reraised exception';
END
$$;

Both blocks "saw" the exception — the inner did its local work, then handed off to the outer for the actual recovery. Powerful pattern: log near the source, recover at the right scope.

Inside a handler, three mechanisms tell you about the exception:

DO $$
DECLARE
    v_msg     TEXT;
    v_state   TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
BEGIN
    BEGIN
        INSERT INTO customer (customer_id, name) VALUES (1, 'Duplicate');
    EXCEPTION
        WHEN OTHERS THEN
            -- Quick way: SQLERRM
            RAISE NOTICE 'Quick: %', SQLERRM;

            -- Detailed way: GET STACKED DIAGNOSTICS
            GET STACKED DIAGNOSTICS
                v_msg     = MESSAGE_TEXT,
                v_state   = RETURNED_SQLSTATE,
                v_detail  = PG_EXCEPTION_DETAIL,
                v_hint    = PG_EXCEPTION_HINT,
                v_context = PG_EXCEPTION_CONTEXT;

            RAISE NOTICE 'Detailed message: %',  v_msg;
            RAISE NOTICE 'SQLSTATE:         %',  v_state;
            RAISE NOTICE 'Detail:           %',  v_detail;
            RAISE NOTICE 'Hint:             %',  v_hint;
    END;
END
$$;

GET STACKED DIAGNOSTICS reaches into the structured detail of the error — particularly useful for the DETAIL and HINT fields, which often contain critical info that isn't in the main message text.

A common use of nested blocks: try one approach, fall back to another if it fails. Useful when you have a "preferred" path and a "safe" path:

DO $$
DECLARE
    target_id INT := 1;
    new_name  TEXT := 'Updated Name';
    rows_changed INT;
BEGIN
    -- Try a strict update first; if no row exists, fall through to insert
    BEGIN
        UPDATE customer
        SET    name = new_name
        WHERE  customer_id = target_id;

        GET DIAGNOSTICS rows_changed = ROW_COUNT;

        IF rows_changed = 0 THEN
            RAISE no_data_found;       -- raise to enter the fallback
        END IF;

        RAISE NOTICE 'Updated existing row';
    EXCEPTION
        WHEN no_data_found THEN
            -- Row didn't exist — fall back to INSERT
            INSERT INTO customer (customer_id, name)
            VALUES (target_id, new_name);
            RAISE NOTICE 'Inserted new row instead';
    END;
END
$$;

1. Catch where you can recover; reraise everything else. A handler that doesn't actually know what to do is worse than no handler — it can swallow real bugs.
2. Use nested blocks to scope error recovery, not just to organize code. The block boundary is also where DML rollback happens, so think about what state you want preserved across the block.
3. Use RAISE; (bare) to reraise inside a handler. If you've logged what you can locally but the actual recovery happens further up, this hands the exception off cleanly.
4. Use GET STACKED DIAGNOSTICS when you need DETAIL, HINT, or context. SQLERRM alone gives you only the message text.
5. Don't catch WHEN OTHERS without good reason. A specific WHEN list documents what you've considered. OTHERS alone obscures.
6. Be aware that an inner block's DML is rolled back when an exception escapes. The outer block's handler sees the state from before the inner block ran.

• An unhandled exception in a block propagates to the enclosing block, then to its enclosing block, until something handles it or it reaches the outermost level.
• An inner block with a matching handler "swallows" the exception — the outer block's normal flow continues.
• An inner block whose handlers don't match (or that has no handlers) lets the exception propagate up.
• A bare RAISE; in a handler reraises the current exception — useful for "log here, recover up there."
• SQLERRM IS a valid PL/pgSQL identifier — it returns the message text of the current exception. GET STACKED DIAGNOSTICS is a more powerful alternative when you need structured access to all error fields.
• An inner block's DML is rolled back to a savepoint when an exception escapes — the outer block sees the pre-block state.