PostgreSQL Block Labels PostgreSQL · Anonymous Block · Labels

PL/pgSQL Block Labels

Master PL/pgSQL block labels — the <<label>> syntax placed immediately before BEGIN (NOT before $$), used for disambiguating shadowed variable names via outer.variable, EXIT label and CONTINUE label for multi-level loop control, and labeled exception scopes. Worked examples include shadowed-variable resolution, outer-loop EXIT, outer-loop CONTINUE, and labeled error counters.

What Block Labels Are

A label is an optional name attached to a PL/pgSQL block (or to a loop) that lets you refer to that specific block from elsewhere in the code. The two main use cases:

Disambiguating shadowed names. If an inner block declares a variable with the same name as one in the outer block, qualifying as outer_label.variable reaches the outer one explicitly.
Targeting nested-loop control. EXIT label and CONTINUE label can break out of (or skip an iteration of) a specific level of nested loops, not just the innermost one.

Syntax

A label is written as an identifier between double angle brackets, placed immediately before the BEGIN keyword:

<<label_name>> BEGIN -- block body END label_name;

The label name after END is optional but recommended — it confirms which block is closing and helps when blocks are nested deeply.

⚠️ The label goes after $$, not before. The original tutorial showed <<label>> $$ DECLARE ... $$; — that's wrong. $$ is just a string delimiter; what comes between the dollar signs is the function body, and the label belongs inside that body, immediately before BEGIN (or DECLARE if there's a declarations section). For functions: $$ <<label>> DECLARE ... BEGIN ... END label; $$.

Examples

Example 1 — Reaching an Outer Variable

An inner block redeclares counter, shadowing the outer one. The label outer on the outer block lets the inner block still reach the outer counter via outer.counter:

PL/pgSQL — labeled outer block

DO $$
<>
DECLARE
    counter INTEGER := 100;
BEGIN
    RAISE NOTICE 'Outer counter starts at %', counter;

    DECLARE
        counter INTEGER := 1;        -- Shadows outer.counter
    BEGIN
        RAISE NOTICE 'Inner counter:        %', counter;
        RAISE NOTICE 'Outer counter (via label): %', outer.counter;

        outer.counter := outer.counter + 5;
    END;

    RAISE NOTICE 'Outer counter is now: %', counter;
END outer;
$$;

OUTPUT

psql — label.variable

postgres=#DO $$ <<outer>> ... $$;

NOTICE: Outer counter starts at 100

NOTICE: Inner counter: 1

NOTICE: Outer counter (via label): 100

NOTICE: Outer counter is now: 105

The inner block both read the outer counter (RAISE NOTICE 'via label') and modified it (outer.counter := outer.counter + 5) — the unqualified name counter inside the inner block always meant the inner one.

Example 2 — EXIT a Specific Loop

With nested loops, plain EXIT exits only the innermost. Labels let you exit a specific outer loop:

PL/pgSQL — EXIT label

DO $$
DECLARE
    i INTEGER;
    j INTEGER;
BEGIN
    <>
    FOR i IN 1 .. 5 LOOP

        <>
        FOR j IN 1 .. 5 LOOP
            RAISE NOTICE 'i=%, j=%', i, j;

            -- Exit the OUTER loop entirely when i=2, j=3
            IF i = 2 AND j = 3 THEN
                EXIT outer_loop;
            END IF;
        END LOOP inner_loop;

    END LOOP outer_loop;

    RAISE NOTICE 'Done.';
END $$;

OUTPUT

psql — exit outer

postgres=#DO $$ ... outer_loop / inner_loop ... $$;

NOTICE: i=1, j=1

NOTICE: i=1, j=2

NOTICE: i=1, j=3

NOTICE: i=1, j=4

NOTICE: i=1, j=5

NOTICE: i=2, j=1

NOTICE: i=2, j=2

NOTICE: i=2, j=3

NOTICE: Done.

When the condition fires at i=2, j=3, control jumps clean out of both loops because EXIT outer_loop targeted the labeled outer one. A plain EXIT would have exited only the inner loop and continued i=2, j=4, i=2, j=5, then proceeded to i=3.

Example 3 — CONTINUE a Specific Loop

CONTINUE label skips the rest of the current iteration of the named loop and starts its next iteration:

PL/pgSQL — CONTINUE label

DO $$
DECLARE
    i INTEGER;
    j INTEGER;
BEGIN
    <>
    FOR i IN 1 .. 3 LOOP
        FOR j IN 1 .. 3 LOOP
            -- If j=2, skip to next i (not next j)
            IF j = 2 THEN
                CONTINUE outer_loop;
            END IF;
            RAISE NOTICE 'i=%, j=%', i, j;
        END LOOP;
    END LOOP outer_loop;
END $$;

OUTPUT

psql — continue outer

postgres=#DO $$ ... CONTINUE outer_loop ... $$;

NOTICE: i=1, j=1

NOTICE: i=2, j=1

NOTICE: i=3, j=1

For each i, the inner loop prints j=1, then hits j=2, and CONTINUE outer_loop jumps directly to the next iteration of the outer loop — skipping not just the rest of the inner iteration but the entire remaining inner loop too.

Example 4 — Labels with Exception Handlers

Labels also help when an exception handler in a nested block needs to refer to outer state:

PL/pgSQL — labeled exception scope

DO $$
<>
DECLARE
    error_count INTEGER := 0;
    i INTEGER;
BEGIN
    FOR i IN 1 .. 5 LOOP
        BEGIN
            -- Force a divide-by-zero on i=3 to demonstrate
            IF i = 3 THEN
                PERFORM 1 / 0;
            END IF;
            RAISE NOTICE 'Processed item %', i;
        EXCEPTION
            WHEN division_by_zero THEN
                main.error_count := main.error_count + 1;
                RAISE NOTICE 'Item % failed (errors so far: %)', i, main.error_count;
        END;
    END LOOP;

    RAISE NOTICE 'Final error count: %', error_count;
END main;
$$;

OUTPUT

psql — labeled handler

postgres=#DO $$ <<main>> ... handler increments main.error_count ... $$;

NOTICE: Processed item 1

NOTICE: Processed item 2

NOTICE: Item 3 failed (errors so far: 1)

NOTICE: Processed item 4

NOTICE: Processed item 5

NOTICE: Final error count: 1

The exception handler runs inside the inner block but updates main.error_count on the outer one. Without the label, this still works (the inner block can see the outer name), but the label makes the intent explicit — the reader knows immediately that this is "outer state being maintained from a nested handler."

Where Labels Can Go

Construct	Label syntax
Block	`<<name>> BEGIN ... END name;`
LOOP	`<<name>> LOOP ... END LOOP name;`
WHILE loop	`<<name>> WHILE cond LOOP ... END LOOP name;`
FOR loop	`<<name>> FOR i IN 1..n LOOP ... END LOOP name;`
FOREACH loop	`<<name>> FOREACH x IN ARRAY ... LOOP ... END LOOP name;`

Best Practices

Place the label immediately before BEGIN (after $$ for top-level functions, or after the parent's BEGIN for nested blocks). The original tutorial put labels in wrong places — the syntax is fixed: <<label>> goes right where DECLARE or BEGIN would otherwise begin.
Match labels at END. END label_name; documents intent and catches mistakes — Postgres will error if the closing label doesn't match the opening one.
Use labels for clarity, not just necessity. Even when not strictly required, a label on a deeply nested block makes the code far easier to follow.
Use labels for all multi-level loop control — EXIT and CONTINUE with labels make intent unambiguous; without labels, the reader has to count nested LOOP keywords.
Don't reuse label names across nested levels. Pick distinct names (outer_loop, inner_loop) so any reference is unambiguous.
Don't put a stray semicolon after BEGIN. BEGIN; is a syntax error. The original tutorial had this; BEGIN is followed directly by the first statement, not a semicolon.

Conclusion

A label <<name>> tags a block or loop so it can be referenced elsewhere in the code.
The label goes immediately before BEGIN (or DECLARE if a declarations section is present) — not before $$.
Use label.variable to reach a shadowed outer variable from inside a nested block.
EXIT label and CONTINUE label control nested loops at a specific level — much clearer than counting LOOP keywords.
Label any deeply nested block for code clarity, even when you don't strictly need it.
Match the label at END label_name; for self-documenting blocks and to catch mistakes early.