SQL LOOP

What is the SQL LOOP statement?

Iterative control statement in PL/pgSQL that repeatedly executes a block of statements until an explicit EXIT is reached.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LOOP: PostgreSQL (PL/pgSQL): Yes Oracle (PL/SQL): Similar LOOP construct MySQL: Only inside stored programs; uses LOOP keyword but slightly different syntax SQL Server, SQLite, Standard SQL: Not supported

SQL LOOP Full Explanation

LOOP is a procedural control-flow construct available in PostgreSQL's PL/pgSQL (and Oracle's PL/SQL) that runs a block of code repeatedly. Unlike FOR or WHILE, LOOP itself has no built-in termination condition; the block continues indefinitely until an EXIT, EXIT WHEN, or RAISE EXCEPTION halts execution. Because LOOP runs inside functions, procedures, or DO blocks, it executes on the server, avoiding client round-trips. Use it when you need flexible, conditionally controlled iteration that cannot be expressed as a set-based SQL operation. Overuse of LOOP can hurt performance, so prefer set operations when possible.

SQL LOOP Syntax

[label :] LOOP
    statement1;
    statement2;
    -- optional EXIT WHEN condition
END LOOP [label];

SQL LOOP Parameters

label (optional) - text - An identifier that lets you reference the loop for EXIT or CONTINUE control.

Example Queries Using SQL LOOP

-- Simple counter using LOOP in a DO block
DO $$
DECLARE
    i int := 1;
BEGIN
    LOOP
        RAISE NOTICE 'i = %', i;
        i := i + 1;
        EXIT WHEN i > 5;  -- stop after 5 iterations
    END LOOP;
END $$;

-- Nested loops with labels
DO $$
DECLARE
    x int := 1;
    y int;
BEGIN
outer_loop:
    LOOP
        EXIT WHEN x > 3;
        y := 1;
inner_loop:
        LOOP
            RAISE NOTICE 'x=%, y=%', x, y;
            y := y + 1;
            EXIT inner_loop WHEN y > 2;
        END LOOP inner_loop;
        x := x + 1;
    END LOOP outer_loop;
END $$;

Expected Output Using SQL LOOP

Each RAISE NOTICE prints the current counter values
After the EXIT condition becomes true, control leaves the loop and the block finishes without error

Use Cases with SQL LOOP

Iterating until complex, multi-step conditions are satisfied.
Consuming rows from a cursor when FETCH status must be checked each time.
Performing retry logic for transient errors.
Building procedural utilities that cannot be expressed in a single SQL statement.

Common Mistakes with SQL LOOP

Forgetting an EXIT statement, causing an infinite loop.
Using LOOP outside a PL/pgSQL context; plain SQL clients will error.
Choosing LOOP over set-based SQL when a simple UPDATE or INSERT ... SELECT would perform better.
Misplacing labels so EXIT targets the wrong loop level.

First Introduced In

PostgreSQL 7.0 (PL/pgSQL initial release)

Frequently Asked Questions

What is the LOOP statement in PostgreSQL?

LOOP is a PL/pgSQL control structure that repeats a block of code until an explicit EXIT or EXIT WHEN condition stops it.

How do I break out of a LOOP?

Place EXIT or EXIT WHEN inside the loop body. When the condition evaluates to true, PostgreSQL exits that loop level.

Does LOOP work in plain SQL scripts?

No. LOOP runs only inside PL/pgSQL contexts such as functions, procedures, or DO blocks. Running it in a plain psql script outside those contexts raises a syntax error.

When should I use LOOP instead of WHILE or FOR?

Use LOOP when the termination condition is complex or evaluated mid-loop, or when you need multiple exit points. If you have a simple counter or cursor iteration, WHILE or FOR is more concise.