PL/pgSQL Nested Blocks

Master PL/pgSQL nested blocks — the localized variable scope and exception handling they provide. Covers proper PostgreSQL types (NUMERIC and INTEGER, not Oracle's NUMBER), variable visibility rules, the canonical try/catch-per-item pattern using FOREACH plus a nested EXCEPTION block, and best practices for when to nest.

A nested block is a complete BEGIN ... END block placed inside another block's body. Nesting is a structural tool that gives you:

• Localized variable scope. A variable declared inside a nested block exists only there — it can't leak into surrounding code.
• Localized exception handling. An EXCEPTION clause in a nested block catches errors only from that block; outer code keeps running normally.
• Logical grouping. Splitting a long block into named, labeled sub-blocks makes intent clearer.

Both blocks can have their own DECLARE section and EXCEPTION handlers. The inner block's END returns control to the outer block, which carries on with whatever comes after the inner END;.

The outer block declares outer_variable with type NUMERIC; the inner block declares its own inner_variable:

DO $$
DECLARE
    outer_variable NUMERIC := 10;
BEGIN
    RAISE NOTICE 'Outer block — outer_variable = %', outer_variable;

    DECLARE
        inner_variable NUMERIC := 20;
    BEGIN
        RAISE NOTICE 'Inner block — outer_variable = %', outer_variable;
        RAISE NOTICE 'Inner block — inner_variable = %', inner_variable;

        -- Modify the outer variable from the inner block
        outer_variable := outer_variable + inner_variable;
        RAISE NOTICE 'Inner block — modified outer_variable = %', outer_variable;
    END;

    RAISE NOTICE 'Outer block — outer_variable is now %', outer_variable;
    -- inner_variable no longer exists here; reading it would be a compile error
END $$;

The most powerful use of nested blocks: catch errors locally without aborting the whole operation. Here, parsing an array of strings — some invalid — reports each failure but continues processing:

DO $$
DECLARE
    inputs TEXT[] := ARRAY['100', 'abc', '200', '3.14', '300'];
    item   TEXT;
    parsed INTEGER;
    total  INTEGER := 0;
    errors INTEGER := 0;
BEGIN
    FOREACH item IN ARRAY inputs LOOP
        BEGIN
            parsed := item::INTEGER;
            total  := total + parsed;
            RAISE NOTICE 'Parsed % -> running total %', item, total;
        EXCEPTION
            WHEN invalid_text_representation THEN
                errors := errors + 1;
                RAISE NOTICE 'Could not parse "%" — skipped', item;
        END;
    END LOOP;

    RAISE NOTICE '----------------------------';
    RAISE NOTICE 'Total: %, Errors: %', total, errors;
END $$;

Without the inner block's exception handler, the first invalid value ('abc') would have aborted the entire DO block. The nested handler isolates each parse attempt — a failure on one item leaves the rest of the loop running.

Compute a factorial inside a nested block whose locals don't pollute the outer scope:

DO $$
DECLARE
    n      INTEGER := 5;
    result BIGINT;
BEGIN
    -- Nested block whose locals (i, accumulator) don't leak
    DECLARE
        i           INTEGER;
        accumulator BIGINT := 1;
    BEGIN
        FOR i IN 1 .. n LOOP
            accumulator := accumulator * i;
        END LOOP;
        result := accumulator;
    END;

    -- Here, i and accumulator no longer exist; only result remains
    RAISE NOTICE '% factorial = %', n, result;
END $$;

The outer block stays clean — only n and result are declared at that level. The loop counter i and the accumulator are scoped to the nested block where they actually matter.

Two sequential nested blocks can compute related values without their working variables clashing:

DO $$
DECLARE
    n INTEGER := 7;
BEGIN
    DECLARE
        result INTEGER;
    BEGIN
        result := n * n;
        RAISE NOTICE 'Square of % is %', n, result;
    END;

    DECLARE
        result INTEGER;
    BEGIN
        result := n * n * n;
        RAISE NOTICE 'Cube of % is %',   n, result;
    END;
END $$;

Both nested blocks declare their own result variable. They don't conflict because each result exists only within its own block — by the time the second block is reached, the first block's result has already been destroyed.

1. Use nested blocks for localized exception handling. The "try parsing this item; on failure, log and continue" pattern is the most common reason to nest.
2. Use nested blocks for short-lived locals. A loop counter or accumulator that's only needed for a few statements doesn't belong in the outer scope.
3. Don't nest unnecessarily. If an inner block declares no variables and has no EXCEPTION clause, it isn't really doing anything — flatten it.
4. Use distinct variable names across nesting levels — shadowing the outer variable usually creates more bugs than it prevents. Reach for labels (see the previous page) only when you genuinely need to.
5. Use Postgres types, not Oracle types. NUMERIC and INTEGER, not NUMBER; VARCHAR(n) or TEXT, not VARCHAR2(n).
6. Label deeply nested blocks so the closing END tags announce which block is ending — much easier to read at scale.

• A nested block is a complete BEGIN ... END placed inside another block's body. It can have its own DECLARE and EXCEPTION clauses.
• Variables in the outer block are visible to inner blocks (unless shadowed); inner-block variables don't leak outwards.
• The chief use is localized error handling — an EXCEPTION clause in the inner block catches only errors raised within that block, leaving the outer block to continue.
• PostgreSQL uses NUMERIC / INTEGER, not Oracle's NUMBER. Match types to the database you're targeting.
• Don't nest unnecessarily — if a block has no locals and no exception handler, it's not doing anything useful.