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

In PL/pgSQL, use the WHILE loop to repeat a block of code as long as a condition is true. The syntax is WHILE condition LOOP ... END LOOP;. This loop runs repeatedly until the condition becomes false.

Syntax

The WHILE loop in PL/pgSQL repeatedly executes the code inside the loop as long as the given condition is true.

• WHILE condition LOOP: Starts the loop and checks the condition.
• statements: The code to run each time the condition is true.
• END LOOP;: Ends the loop block.

sql

WHILE condition LOOP
    -- statements to execute
END LOOP;

Example

This example shows a PL/pgSQL function that uses a WHILE loop to count from 1 to 5 and return the sum of these numbers.

sql

CREATE OR REPLACE FUNCTION sum_while_loop() RETURNS INTEGER AS $$
DECLARE
    counter INTEGER := 1;
    total INTEGER := 0;
BEGIN
    WHILE counter <= 5 LOOP
        total := total + counter;
        counter := counter + 1;
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT sum_while_loop();

Output

 sum_while_loop 
--------------
           15
(1 row)

Common Pitfalls

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

• Forgetting to update the loop variable inside the loop, causing an infinite loop.
• Using a condition that never becomes false.
• Not initializing variables properly before the loop.

Always ensure the loop condition will eventually become false to avoid infinite loops.

sql

/* Wrong: Infinite loop because counter is not updated */
CREATE OR REPLACE FUNCTION infinite_loop() RETURNS VOID AS $$
DECLARE
    counter INTEGER := 1;
BEGIN
    WHILE counter <= 5 LOOP
        RAISE NOTICE 'Counter: %', counter;
        -- Missing counter := counter + 1;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

/* Correct: Update counter inside the loop */
CREATE OR REPLACE FUNCTION fixed_loop() RETURNS VOID AS $$
DECLARE
    counter INTEGER := 1;
BEGIN
    WHILE counter <= 5 LOOP
        RAISE NOTICE 'Counter: %', counter;
        counter := counter + 1;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Quick Reference

Part	Description
WHILE condition LOOP	Starts the loop and checks if condition is true
statements	Code executed each time the condition is true
END LOOP;	Ends the loop block
Loop variable	Variable controlling the loop, must be updated inside
Condition	Boolean expression that controls loop continuation

Key Takeaways

Use WHILE loops to repeat code while a condition remains true in PL/pgSQL.

Always update the loop variable inside the loop to avoid infinite loops.

Initialize variables before the loop to ensure correct behavior.

The loop ends when the condition becomes false.

Use RAISE NOTICE to debug loop execution in PL/pgSQL.