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

In PL/pgSQL, you use the LOOP statement to repeat a block of code indefinitely until you exit it with EXIT. You can also use FOR loops to iterate over a range or query results. Loops help automate repetitive tasks inside PostgreSQL functions.

Syntax

The basic LOOP syntax in PL/pgSQL repeats statements until an EXIT condition is met. You can also use FOR loops to iterate over a range or query rows.

• LOOP: Starts an infinite loop.
• EXIT WHEN condition;: Stops the loop when the condition is true.
• END LOOP;: Ends the loop block.
• FOR var IN [REVERSE] start..end LOOP: Loops over a numeric range.
• FOR record IN query LOOP: Loops over query result rows.

sql

LOOP
  -- statements
  EXIT WHEN condition;
END LOOP;

FOR counter IN [REVERSE] start..end LOOP
  -- statements
END LOOP;

FOR record IN SELECT * FROM table LOOP
  -- statements
END LOOP;

Example

This example shows a simple LOOP that counts from 1 to 5 and prints each number using RAISE NOTICE. It demonstrates how to use EXIT WHEN to stop the loop.

plpgsql

DO $$
DECLARE
  counter INTEGER := 1;
BEGIN
  LOOP
    RAISE NOTICE 'Counter value: %', counter;
    EXIT WHEN counter > 5;
    counter := counter + 1;
  END LOOP;
END $$;

Output

NOTICE:  Counter value: 1
NOTICE:  Counter value: 2
NOTICE:  Counter value: 3
NOTICE:  Counter value: 4
NOTICE:  Counter value: 5

Common Pitfalls

Common mistakes when using loops in PL/pgSQL include:

• Forgetting to include an EXIT condition in a LOOP, causing an infinite loop.
• Using the wrong loop type for the task, like a LOOP instead of a FOR loop for iterating over query results.
• Not initializing loop counters properly.

plpgsql

/* Wrong: Infinite loop without EXIT */
DO $$
BEGIN
  LOOP
    RAISE NOTICE 'Infinite loop';
    -- Missing EXIT condition
  END LOOP;
END $$;

/* Correct: Adding EXIT condition */
DO $$
DECLARE
  i INTEGER := 1;
BEGIN
  LOOP
    RAISE NOTICE 'Count: %', i;
    EXIT WHEN i > 3;
    i := i + 1;
  END LOOP;
END $$;

Output

NOTICE:  Count: 1
NOTICE:  Count: 2
NOTICE:  Count: 3

Quick Reference

Loop Type	Description	Example Usage
LOOP	Repeats until EXIT condition	LOOP ... EXIT WHEN condition; END LOOP;
FOR numeric	Iterates over a numeric range	FOR i IN 1..5 LOOP ... END LOOP;
FOR query	Iterates over query result rows	FOR rec IN SELECT * FROM table LOOP ... END LOOP;

Key Takeaways

Use LOOP with EXIT WHEN to control repetition in PL/pgSQL.

Choose FOR loops for iterating over ranges or query results for cleaner code.

Always ensure loops have exit conditions to avoid infinite loops.

Use RAISE NOTICE to debug and see loop progress during development.