PL/pgSQL LOOP

Master the PL/pgSQL unconditional LOOP — basic LOOP with EXIT and EXIT WHEN, CONTINUE for skipping iterations, labelled loops for nested structures, and the proper EXIT label syntax (no double-angle brackets when referencing a label).

The basic LOOP statement in PL/pgSQL is unconditional — it just keeps running until something inside it tells it to stop. That something is usually EXIT, RETURN, or an exception. Compare to WHILE (conditional at the top) and FOR (range or query-driven), each covered on its own page.

Why use plain LOOP? Two reasons:

• The exit condition is checked in the middle of the body, not at the top or after a fixed range — for example, "fetch a row, process it, exit if it failed validation."
• You want maximum flexibility — multiple exit points, complex termination logic, or labelled loops for breaking out of nested structures.

Or, more compactly, with EXIT WHEN:

Three control statements live inside loops:

The textbook first example. Increment a counter and stop at 5:

DO $$
DECLARE
    counter INT := 0;
BEGIN
    LOOP
        counter := counter + 1;
        RAISE NOTICE '%', counter;
        IF counter = 5 THEN
            EXIT;
        END IF;
    END LOOP;
END
$$;

The same logic, but the IF + EXIT pair is collapsed into a single EXIT WHEN:

DO $$
DECLARE
    counter INT := 0;
BEGIN
    LOOP
        counter := counter + 1;
        RAISE NOTICE '%', counter;
        EXIT WHEN counter = 5;
    END LOOP;
END
$$;

A loop can be labelled with <<label_name>> placed immediately before the LOOP keyword. EXIT and CONTINUE can then take a label as argument, specifying which loop they affect.

DO $$
DECLARE
    counter INT := 0;
BEGIN
    <>
    LOOP
        counter := counter + 1;
        RAISE NOTICE '%', counter;
        EXIT my_loop WHEN counter = 5;   -- note: my_loop, not <>
    END LOOP;
END
$$;

For a single loop, the label adds nothing. Labels earn their keep with nested loops, where EXIT needs to break out of more than just the innermost loop.

Generate a 3×3 grid of (row, col) pairs. The inner loop builds columns; the outer loop builds rows. Labels on both let us terminate each loop precisely:

DO $$
DECLARE
    row_var INT := 0;
    col_var INT := 0;
BEGIN
    <>
    LOOP
        row_var := row_var + 1;

        <>
        LOOP
            col_var := col_var + 1;
            RAISE NOTICE '(%, %)', row_var, col_var;
            EXIT inner_loop WHEN col_var = 3;
        END LOOP;

        col_var := 0;                    -- reset for next row
        EXIT outer_loop WHEN row_var = 3;
    END LOOP;
END
$$;

CONTINUE bails out of the current iteration and starts the next one — useful for "skip this row, move on." Like EXIT, it can take a label.

DO $$
DECLARE
    counter INT := 0;
BEGIN
    LOOP
        counter := counter + 1;
        EXIT WHEN counter > 8;
        CONTINUE WHEN counter % 2 = 0;   -- skip even numbers
        RAISE NOTICE 'Odd: %', counter;
    END LOOP;
END
$$;

1. Always have an exit path. Plain LOOP is unconditional — without an EXIT, RETURN, or exception, you have an infinite loop. Make the exit condition obvious.
2. Prefer EXIT WHEN to IF ... EXIT ... END IF. Same logic, half the lines.
3. Reach for FOR or WHILE first. Plain LOOP wins when the exit condition is in the middle of the body. If it's at the top, use WHILE; if you're iterating a known range or query, use FOR.
4. Label loops only when you need to. Labels matter for nested loops where EXIT would otherwise hit only the innermost. For a single loop, the label is decoration.
5. Don't include << >> when referencing a label in EXIT or CONTINUE. The brackets only appear at the declaration site.
6. Avoid loops when set-based SQL works. Looping a million rows in PL/pgSQL is much slower than one well-written UPDATE. Loops earn their keep when each row needs application logic.

• Plain LOOP is the unconditional looping construct — runs until EXIT, RETURN, or an exception.
• EXIT WHEN condition is the compact form of IF condition THEN EXIT; END IF.
• CONTINUE skips the rest of the current iteration. Both EXIT and CONTINUE can take a loop label as argument.
• Labels are written <<label_name>> at the declaration site, but referenced as plain label_name (no brackets) in EXIT/CONTINUE.
• Labels matter mainly for nested loops — letting an inner-loop EXIT terminate the outer loop too.
• For most cases, FOR or WHILE is cleaner than plain LOOP — reach for plain LOOP when the exit point is mid-body.