PL/pgSQL FOR Loop

Master the PL/pgSQL FOR loop — three forms covering integer ranges (with REVERSE and BY step), query result iteration with implicit cursor management, and dynamic queries via FOR ... IN EXECUTE ... USING with safe parameter binding.

PL/pgSQL's FOR loop comes in three flavors, each suited to a different iteration pattern:

This is the most-used loop construct in PL/pgSQL — both because the syntax is concise and because PostgreSQL handles all the cursor mechanics for you.

Three things to know:

• loop_counter is implicitly declared inside the loop — you don't (and shouldn't) declare it in DECLARE. It exists only inside the loop.
• REVERSE counts down instead of up. Note: with REVERSE, you write high..low, not low..high.
• BY step changes the increment — useful for "every other," "every third," etc. Defaults to 1.

DO $$
BEGIN
    FOR counter IN 1..5 LOOP
        RAISE NOTICE 'counter: %', counter;
    END LOOP;
END
$$;

Both bounds are inclusive — the loop runs for 1, 2, 3, 4, and 5. The variable counter is auto-declared by the FOR statement; it doesn't need to be in DECLARE.

DO $$
BEGIN
    FOR counter IN REVERSE 5..1 LOOP
        RAISE NOTICE 'counter: %', counter;
    END LOOP;
END
$$;

Iterate from 1 to 10 by twos:

DO $$
BEGIN
    FOR counter IN 1..10 BY 2 LOOP
        RAISE NOTICE 'counter: %', counter;
    END LOOP;
END
$$;

The most common FOR loop in real PL/pgSQL code: walking through the rows returned by a SELECT.

target can be a record variable, a %ROWTYPE variable, or a comma-separated list of scalar variables. PostgreSQL implicitly opens a cursor, fetches one row per iteration into target, and closes the cursor when the loop ends.

Setup:

DROP TABLE IF EXISTS film;

CREATE TABLE film (
    title        VARCHAR(255),
    release_year INTEGER,
    length       INTEGER
);

INSERT INTO film (title, release_year, length) VALUES
    ('Film 1', 1999, 142),
    ('Film 2', 2010, 175),
    ('Film 3', 2008, 152),
    ('Film 4', 2003, 154),
    ('Film 5', 2006, 201),
    ('Film 6', 2009, 195),
    ('Film 7', 2020, 148),
    ('Film 8', 2022, 139),
    ('Film 9', 2015, 142),
    ('Film 10', 2024, 136);

Now walk the result set, printing the top 5 longest films:

DO $$
DECLARE
    rec record;
BEGIN
    FOR rec IN
        SELECT title, length
        FROM   film
        ORDER  BY length DESC
        LIMIT  5
    LOOP
        RAISE NOTICE '% (%min)', rec.title, rec.length;
    END LOOP;
END
$$;

When the query string itself is built at runtime — based on input, configuration, or user choice — use FOR ... IN EXECUTE:

USING binds parameters safely — values are sent separately from the SQL text, so they can never be reinterpreted as code. This is your defense against SQL injection when building dynamic queries.

DO $$
DECLARE
    sort_type SMALLINT := 1;     -- 1 = title, 2 = release_year
    rec_count INTEGER  := 5;
    rec       record;
    query     TEXT;
BEGIN
    query := 'SELECT title, release_year FROM film ';

    IF sort_type = 1 THEN
        query := query || 'ORDER BY title';
    ELSIF sort_type = 2 THEN
        query := query || 'ORDER BY release_year';
    ELSE
        RAISE EXCEPTION 'Invalid sort type: %', sort_type;
    END IF;

    query := query || ' LIMIT $1';

    FOR rec IN EXECUTE query USING rec_count LOOP
        RAISE NOTICE '% - %', rec.release_year, rec.title;
    END LOOP;
END
$$;

When you're walking rows from one specific table, a %ROWTYPE variable gives you compile-time field-name validation that record defers to runtime:

Mark orders older than 30 days as Completed. Setup:

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id   INTEGER PRIMARY KEY,
    order_date DATE,
    status     VARCHAR(50)
);

INSERT INTO orders (order_id, order_date, status) VALUES
    (1,  '2025-08-01', 'Pending'),
    (2,  '2025-09-15', 'Processing'),
    (3,  '2025-10-10', 'Shipped'),
    (4,  '2025-11-20', 'Pending'),
    (5,  '2025-12-05', 'Processing'),
    (6,  '2026-01-30', 'Shipped'),
    (7,  '2026-02-10', 'Pending'),
    (8,  '2026-03-25', 'Processing'),
    (9,  '2026-04-05', 'Shipped'),
    (10, '2026-04-30', 'Pending');

DO $$
DECLARE
    order_record orders%ROWTYPE;
BEGIN
    FOR order_record IN
        SELECT * FROM orders
        WHERE  order_date <= current_date - INTERVAL '30 days'
          AND  status <> 'Completed'
    LOOP
        UPDATE orders
        SET    status = 'Completed'
        WHERE  order_id = order_record.order_id;

        RAISE NOTICE 'Order ID %: status updated to Completed', order_record.order_id;
    END LOOP;
END
$$;

1. Don't declare the loop counter in DECLARE. The FOR statement implicitly declares it; an explicit declaration is redundant or shadowing.
2. Use FOR rec IN SELECT ... LOOP as the default for walking query results — cleaner than explicit cursors.
3. Use %ROWTYPE over record when the row shape is fixed — gives you compile-time field validation.
4. Use EXECUTE ... USING for dynamic queries. Never concatenate user input into the SQL string — that's a SQL injection risk.
5. With REVERSE, write bounds high..low. The first number is the starting value; REVERSE just changes the direction.
6. Prefer set-based SQL when possible. A single UPDATE covering many rows is faster than a FOR loop running individual UPDATEs. Loops earn their keep when each row needs application-level logic.

• FOR has three forms: integer range (FOR i IN 1..5), query result (FOR rec IN SELECT), and dynamic query (FOR rec IN EXECUTE).
• The loop variable is implicitly declared by the FOR statement — don't put it in DECLARE.
• Both bounds in a range are inclusive. REVERSE counts down (write high..low). BY step changes the increment.
• For query iteration, PostgreSQL implicitly manages the cursor — open, fetch, close. You just write the body.
• Use %ROWTYPE for fixed shapes; use record for flexible shapes.
• EXECUTE ... USING is the safe pattern for dynamic SQL — values pass as parameters, never as injected text.
• For uniform updates over many rows, set-based SQL beats any FOR loop.