FOR over query rows in SQL

Visit each order as a record: the loop variable holds one row at a time.

-- FOR row IN query LOOP visits each result row as a record.
DO $$
DECLARE
    line RECORD;
BEGIN
    FOR line IN
        SELECT o.id, c.name, o.total
        FROM orders AS o
        JOIN customers AS c ON c.id = o.customer_id
        ORDER BY o.id
    LOOP
        RAISE NOTICE 'order % (%) totals %', line.id, line.name,
            line.total;
    END LOOP;
END;
$$;

How it works

1. line RECORD declares a variable shaped by whatever the query returns.
2. FOR line IN ... LOOP runs the body once per row, in ORDER BY order.
3. Fields read as line.id and line.name inside the loop.

Keywords and builtins used here

• AS
• BEGIN
• BY
• DECLARE
• DO
• END
• FOR
• FROM
• IN
• JOIN
• ON
• ORDER
• SELECT
• c

The run, in numbers

Lines

16

Characters to type

302

Tokens

70

Three-star pace

65 tpm

At the three-star pace of 65 tokens a minute, this run takes about 65 seconds.

Type this snippet

Step 3 of 4 in Queries in code, step 15 of 23 in PostgreSQL Stored Procedures.

← Previous Next →