PL/pgSQL WHILE Loop

Master the PL/pgSQL WHILE loop — pre-test loop semantics, real-world example walking pending tasks, infinite loop pitfalls and how to avoid them, and when WHILE beats FOR or set-based SQL.

A WHILE loop runs its body as long as a condition is true. The condition is checked before each iteration — if it's false (or NULL) on the first check, the body never executes at all. This is the "pre-test loop" pattern.

Three things to remember:

• The condition is checked before each iteration. If false on entry, the body is skipped entirely.
• Something inside the body must change so the condition eventually becomes false — otherwise you have an infinite loop.
• NULL is treated as not-true. A condition that evaluates to NULL terminates the loop, just like FALSE.

Display the value of a counter from 0 up to (but not including) 5:

DO $$
DECLARE
    counter INTEGER := 0;
BEGIN
    WHILE counter < 5 LOOP
        RAISE NOTICE 'Counter %', counter;
        counter := counter + 1;
    END LOOP;
END
$$;

Five iterations, then counter reaches 5, the condition becomes false, and the loop exits. The "pre-test" nature: when counter = 5 the condition is checked first, finds false, and the body doesn't run again.

Consider a tasks table where each task has a status. The WHILE pattern shines when you need to keep processing as long as work remains, without knowing in advance how much work that is.

DROP TABLE IF EXISTS tasks;

CREATE TABLE tasks (
    task_id   SERIAL PRIMARY KEY,
    task_name VARCHAR(100),
    status    VARCHAR(20)
);

INSERT INTO tasks (task_name, status) VALUES
    ('Review Report',         'Pending'),
    ('Prepare Presentation',  'Completed'),
    ('Update Documentation',  'Pending'),
    ('Conduct Meeting',       'Pending'),
    ('Analyze Data',          'Pending'),
    ('Develop Prototype',     'Completed'),
    ('Test Software',         'Pending'),
    ('Write Code',            'Pending'),
    ('Review Code',           'Completed'),
    ('Plan Project',          'Pending');

The pattern: read the next pending task ID; if there is one, process it; loop until none remain.

DO $$
DECLARE
    task_id_to_process INT;
BEGIN
    -- Get the first pending task
    SELECT MIN(task_id) INTO task_id_to_process
    FROM   tasks WHERE status = 'Pending';

    -- Loop until no more pending tasks
    WHILE task_id_to_process IS NOT NULL LOOP
        UPDATE tasks
        SET    status = 'Completed'
        WHERE  task_id = task_id_to_process;

        RAISE NOTICE 'Task ID % has been completed.', task_id_to_process;

        -- Get next pending task — drives the loop forward
        SELECT MIN(task_id) INTO task_id_to_process
        FROM   tasks WHERE status = 'Pending';
    END LOOP;
END
$$;

Once all 'Pending' tasks have been updated, SELECT MIN(task_id) ... WHERE status = 'Pending' returns no rows, task_id_to_process becomes NULL, and the loop exits. NULL is treated as not-true in the WHILE condition.

Sometimes you genuinely need WHILE — when each iteration depends on results from the previous one. Example: doubling a value until it exceeds a threshold:

DO $$
DECLARE
    value      INTEGER := 1;
    iterations INTEGER := 0;
    target     INTEGER := 1000;
BEGIN
    WHILE value < target LOOP
        value := value * 2;
        iterations := iterations + 1;
        RAISE NOTICE 'After % doublings: %', iterations, value;
    END LOOP;

    RAISE NOTICE 'Reached % in % iterations', value, iterations;
END
$$;

You don't know up front how many iterations are needed — that's exactly when WHILE is the right tool. Each iteration's value depends on the previous one's, and the termination condition is data-driven.

The most common WHILE bug: the condition can never become false. Three patterns to watch for:

-- Bug 1: forgot to update the loop variable
DECLARE counter INT := 0;
BEGIN
    WHILE counter < 5 LOOP
        RAISE NOTICE '%', counter;
        -- counter is never incremented — infinite loop
    END LOOP;
END;

-- Bug 2: wrong direction
DECLARE counter INT := 5;
BEGIN
    WHILE counter < 10 LOOP
        counter := counter - 1;        -- moves AWAY from 10
    END LOOP;
END;

-- Bug 3: condition can become NULL but the body assumes it eventually goes false
DECLARE x INTEGER := 5;
BEGIN
    WHILE x > 0 LOOP
        x := some_function_returning_null();   -- NULL > 0 is NULL → loop exits
        -- but is that what you intended?
    END LOOP;
END;

1. Identify what makes the condition false before writing the body. If you can't point at the line, the loop is at risk of running forever.
2. Initialize the loop variable before the loop. Don't depend on default values to control loop entry — set it explicitly.
3. Don't use WHILE when set-based SQL works. One UPDATE ... WHERE usually beats a row-by-row loop.
4. Use FOR for known integer ranges and FOR rec IN SELECT for query iteration. WHILE is for everything else.
5. Be careful with NULL. A WHILE condition that evaluates to NULL exits the loop — sometimes that's what you want, sometimes it's a hidden bug.
6. Add a safety counter for unbounded loops in production code: EXIT WHEN iterations > MAX_ITERATIONS. Defends against a logic bug that would otherwise lock up the database.

• WHILE condition LOOP runs its body as long as the condition is true. It's the pre-test loop — condition is checked before each iteration, including the first.
• If the condition is false (or NULL) on entry, the body never executes.
• Inside the body, something must change so the condition eventually becomes false — otherwise you have an infinite loop.
• NULL counts as not-true: a NULL condition exits the loop just like FALSE.
• WHILE shines when each iteration depends on the previous one's result. For known ranges or query iteration, prefer FOR.
• For uniform row updates, set-based SQL beats any loop.