PostgresqlHow-ToBeginner · 3 min read

How to Use For Loop in PL/pgSQL: Syntax and Examples

In PL/pgSQL, use the FOR loop to repeat a block of code a set number of times or over a query result. The loop can iterate over a range of integers or rows returned by a query using FOR variable IN ... LOOP syntax. This helps automate repetitive tasks inside PostgreSQL functions.

📐

Syntax

The FOR loop in PL/pgSQL has two main forms:

Numeric loop: Iterates over a range of integers.
Query loop: Iterates over each row returned by a query.

Each loop uses FOR variable IN ... LOOP and ends with END LOOP;.

sql

FOR counter IN [start_value]..[end_value] LOOP
    -- statements
END LOOP;

-- or for query results
FOR record_variable IN SELECT * FROM table_name LOOP
    -- statements using record_variable
END LOOP;

💻

Example

This example shows a numeric FOR loop that prints numbers 1 to 5, and a query loop that prints employee names from a table.

plpgsql

DO $$
DECLARE
    i INTEGER;
    emp RECORD;
BEGIN
    -- Numeric loop from 1 to 5
    FOR i IN 1..5 LOOP
        RAISE NOTICE 'Number: %', i;
    END LOOP;

    -- Query loop over employees table
    FOR emp IN SELECT first_name, last_name FROM employees LOOP
        RAISE NOTICE 'Employee: % %', emp.first_name, emp.last_name;
    END LOOP;
END $$;

Output

NOTICE: Number: 1 NOTICE: Number: 2 NOTICE: Number: 3 NOTICE: Number: 4 NOTICE: Number: 5 NOTICE: Employee: John Doe NOTICE: Employee: Jane Smith

⚠️

Common Pitfalls

Common mistakes when using FOR loops in PL/pgSQL include:

Forgetting to declare the loop variable.
Using incorrect range syntax (e.g., missing ..).
Not ending the loop with END LOOP;.
Confusing numeric loops with query loops.

Always declare variables and use proper syntax to avoid errors.

plpgsql

/* Wrong: missing loop variable declaration and wrong range syntax */
DO $$
BEGIN
    FOR i IN 1-5 LOOP  -- incorrect range
        RAISE NOTICE 'Number: %', i;
    END LOOP;
END $$;

/* Correct: declare variable and use proper range */
DO $$
DECLARE
    i INTEGER;
BEGIN
    FOR i IN 1..5 LOOP
        RAISE NOTICE 'Number: %', i;
    END LOOP;
END $$;

📊

Quick Reference

Usage	Syntax Example	Description
Numeric loop	FOR i IN 1..10 LOOP ... END LOOP;	Loops from 1 to 10 inclusive.
Query loop	FOR rec IN SELECT * FROM table LOOP ... END LOOP;	Loops over each row from a query.
Loop variable	DECLARE i INTEGER;	Variable to hold loop counter or row.
End loop	END LOOP;	Marks the end of the loop block.

✅

Key Takeaways

Use FOR loops in PL/pgSQL to repeat code over ranges or query results.

Declare loop variables before using them in the loop.

Numeric loops use two dots (..) to define the range.

Query loops iterate over rows returned by a SELECT statement.

Always close loops with END LOOP; to avoid syntax errors.