LOOP, WHILE, FOR iterations in PostgreSQL - Deep Dive

Overview - LOOP, WHILE, FOR iterations

What is it?

In PostgreSQL, LOOP, WHILE, and FOR are ways to repeat a set of commands multiple times inside a function or procedure. LOOP repeats indefinitely until stopped, WHILE repeats as long as a condition is true, and FOR repeats over a range or query result. These help automate repetitive tasks in the database.

Why it matters

Without loops, you would have to write the same commands many times or run them manually one by one. Loops let the database do repetitive work quickly and reliably, like processing many rows or running calculations multiple times. This saves time and reduces errors.

Where it fits

Before learning loops, you should understand basic SQL commands and how to write simple functions in PostgreSQL. After mastering loops, you can learn about more advanced control structures like conditional statements and exception handling to build complex logic.

Mental Model

Core Idea

Loops in PostgreSQL repeat a block of code multiple times, controlled by conditions or ranges, to automate repetitive database tasks.

Think of it like...

Loops are like a washing machine cycle: it repeats washing steps until the clothes are clean, just like loops repeat commands until a condition is met or a range is done.

┌───────────────┐
│   Start Loop  │
├───────────────┤
│ Execute Block │
├───────────────┤
│ Check Condition│
├───────────────┤
│   Continue?   │─No─> Exit Loop
│     Yes       │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Basic LOOP Structure in PL/pgSQL

Concept: Introduces the simplest LOOP syntax that repeats until explicitly exited.

In PostgreSQL, a LOOP block runs repeatedly until you use EXIT to stop it. Example: CREATE FUNCTION simple_loop() RETURNS void AS $$ DECLARE counter INT := 1; BEGIN LOOP RAISE NOTICE 'Count: %', counter; counter := counter + 1; IF counter > 5 THEN EXIT; -- stops the loop END IF; END LOOP; END; $$ LANGUAGE plpgsql; This prints numbers 1 to 5.

Result

The function prints 'Count: 1' through 'Count: 5' and then stops.

Understanding that LOOP runs endlessly until EXIT is called helps you control repetition precisely.

2

Foundation

WHILE Loop with Condition Check

Concept: Shows how WHILE loops repeat only while a condition remains true.

WHILE loops check a condition before each iteration. Example: CREATE FUNCTION while_loop() RETURNS void AS $$ DECLARE counter INT := 1; BEGIN WHILE counter <= 5 LOOP RAISE NOTICE 'Count: %', counter; counter := counter + 1; END LOOP; END; $$ LANGUAGE plpgsql; This prints numbers 1 to 5.

Result

The function prints 'Count: 1' through 'Count: 5' and stops when counter is 6.

Knowing WHILE checks the condition before running the block prevents infinite loops and controls repetition.

3

Intermediate

FOR Loop Over Integer Range

🤔Before reading on: do you think FOR loops in PostgreSQL can only iterate over arrays, or can they also iterate over number ranges? Commit to your answer.

Concept: Introduces FOR loops that iterate over a sequence of numbers.

FOR loops can iterate over a range of integers easily. Example: CREATE FUNCTION for_loop_range() RETURNS void AS $$ DECLARE i INT; BEGIN FOR i IN 1..5 LOOP RAISE NOTICE 'Count: %', i; END LOOP; END; $$ LANGUAGE plpgsql; This prints numbers 1 to 5.

Result

The function prints 'Count: 1' through 'Count: 5'.

Understanding FOR loops over ranges simplifies repeating tasks a fixed number of times without manual counters.

4

Intermediate

FOR Loop Over Query Results

🤔Before reading on: do you think FOR loops can iterate over rows returned by a SQL query in PostgreSQL? Commit to yes or no.

Concept: Shows how FOR loops can process each row from a query result.

You can loop over rows from a SELECT query. Example: CREATE FUNCTION for_loop_query() RETURNS void AS $$ DECLARE rec RECORD; BEGIN FOR rec IN SELECT id, name FROM users LOOP RAISE NOTICE 'User ID: %, Name: %', rec.id, rec.name; END LOOP; END; $$ LANGUAGE plpgsql; This prints each user's id and name.

Result

The function prints one line per user with their id and name.

Knowing FOR loops can iterate query results lets you process data row-by-row inside the database.

5

Intermediate

Using EXIT and CONTINUE in Loops

Concept: Introduces controlling loop flow with EXIT to stop and CONTINUE to skip to next iteration.

Inside loops, EXIT stops the loop immediately, and CONTINUE skips the rest of the current iteration. Example: CREATE FUNCTION exit_continue_loop() RETURNS void AS $$ DECLARE i INT := 0; BEGIN LOOP i := i + 1; IF i = 3 THEN CONTINUE; -- skip printing 3 END IF; RAISE NOTICE 'Count: %', i; IF i >= 5 THEN EXIT; -- stop after 5 END IF; END LOOP; END; $$ LANGUAGE plpgsql; This prints 1, 2, 4, 5 skipping 3.

Result

Output lines: Count: 1, Count: 2, Count: 4, Count: 5

Understanding EXIT and CONTINUE gives fine control over loop execution flow.

6

Advanced

Nested Loops and Performance Considerations

🤔Before reading on: do you think nested loops always run fast in PostgreSQL, or can they cause performance issues? Commit to your answer.

Concept: Explains how loops inside loops work and their impact on performance.

You can put loops inside loops to handle complex tasks. Example: FOR i IN 1..3 LOOP FOR j IN 1..2 LOOP RAISE NOTICE 'i=%, j=%', i, j; END LOOP; END LOOP; But nested loops multiply iterations (3*2=6 here). In large data, this can slow down your function. Use them carefully and consider set-based SQL alternatives.

Result

Prints pairs of i and j values for all combinations.

Knowing nested loops multiply work helps you avoid slow database functions by choosing better approaches.

7

Expert

Loop Control with Labels and Exception Handling

🤔Before reading on: do you think PostgreSQL supports naming loops with labels to control EXIT or CONTINUE in nested loops? Commit yes or no.

Concept: Shows advanced loop control using labels and how exceptions interact with loops.

PostgreSQL lets you name loops to control which loop to EXIT or CONTINUE in nested cases. Example: <> FOR i IN 1..3 LOOP <> FOR j IN 1..3 LOOP IF i = 2 AND j = 2 THEN EXIT outer_loop; -- exits the outer loop END IF; END LOOP inner_loop; END LOOP outer_loop; Also, exceptions inside loops can break loops unless handled. Use BEGIN...EXCEPTION blocks inside loops to catch errors and continue.

Result

Loop exits early when i=2 and j=2, stopping all iterations.

Understanding loop labels and exception handling prevents bugs in complex nested loops and improves control.

Under the Hood

PostgreSQL loops run inside PL/pgSQL functions by repeatedly executing the block of code. The database engine manages the loop counter or condition checks. For FOR loops over queries, it opens a cursor internally and fetches rows one by one. EXIT and CONTINUE control flow by jumping out or skipping iterations. Exception handling inside loops uses internal try-catch mechanisms to maintain stability.

Why designed this way?

Loops were designed to allow procedural logic inside SQL functions, which are otherwise declarative. This lets users write complex logic close to data for efficiency. The design balances flexibility with safety by requiring explicit EXIT to avoid infinite loops and supporting labels for nested control. Alternatives like pure SQL set operations exist but loops fill gaps where row-by-row logic is needed.

┌───────────────┐
│   Start Loop  │
├───────────────┤
│ Execute Block │
├───────────────┤
│ Check Condition│
├───────────────┤
│   EXIT?       │─Yes─> Exit Loop
│     No        │
├───────────────┤
│ CONTINUE?     │─Yes─> Next Iteration
│     No        │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think a LOOP without EXIT will stop on its own? Commit yes or no.

Common Belief: A LOOP block will automatically stop after some time or iterations even without EXIT.

Reality: A LOOP without an EXIT condition runs forever, causing an infinite loop unless manually stopped.

Why it matters: Infinite loops can freeze the database session, wasting resources and requiring manual intervention.

Quick: Can a FOR loop in PostgreSQL iterate over any data type? Commit yes or no.

Common Belief: FOR loops can iterate over any data type, like strings or complex objects directly.

Reality: FOR loops iterate over integer ranges or query result rows, but not arbitrary data types like strings directly.

Why it matters: Misusing FOR loops with unsupported types causes errors and confusion in function logic.

Quick: Does CONTINUE skip the entire loop or just the current iteration? Commit your answer.

Common Belief: CONTINUE exits the whole loop immediately.

Reality: CONTINUE skips only the rest of the current iteration and proceeds with the next iteration.

Why it matters: Misunderstanding CONTINUE leads to incorrect loop flow and unexpected results.

Quick: Do you think nested loops always perform well on large datasets? Commit yes or no.

Common Belief: Nested loops are efficient and safe to use on large tables without performance issues.

Reality: Nested loops multiply iterations and can cause severe performance degradation on large datasets.

Why it matters: Using nested loops carelessly can slow down or crash production databases.

Expert Zone

1

Labels on loops allow precise control in deeply nested loops, avoiding accidental exits of wrong loops.

2

FOR loops over queries use implicit cursors, which can be tuned for performance by controlling fetch size.

3

Exception handling inside loops can be used to skip problematic rows without stopping the entire batch process.

When NOT to use

Avoid loops when set-based SQL queries can achieve the same result more efficiently. Use window functions, CTEs, or bulk operations instead of row-by-row loops for large data.

Production Patterns

Loops are often used in maintenance tasks like archiving old data, batch updates, or complex calculations that cannot be done in pure SQL. Experts combine loops with exception handling and labels for robust, maintainable functions.

Connections

Recursion

Loops and recursion both repeat actions but recursion does so by calling functions within themselves.

Understanding loops helps grasp recursion as another way to repeat tasks, often interchangeable but with different performance and readability.

Finite State Machines

Loops implement repeated state transitions controlled by conditions, similar to how finite state machines operate.

Knowing loops as state transitions clarifies how complex logic flows can be modeled and debugged.

Assembly Language Loops

Both PostgreSQL loops and assembly loops use jump instructions to repeat code blocks.

Recognizing this low-level similarity deepens understanding of how high-level loops translate to machine operations.

Common Pitfalls

#1 Infinite loop due to missing EXIT condition.

Wrong approach:

LOOP
RAISE NOTICE 'Hello';
END LOOP;

Correct approach:

DECLARE counter INT := 1;
LOOP
RAISE NOTICE 'Hello %', counter;
counter := counter + 1;
IF counter > 5 THEN EXIT; END IF;
END LOOP;

Root cause:

Not including an EXIT condition causes the loop to run forever.

#2 Using CONTINUE to exit loop instead of skipping iteration.

Wrong approach:

LOOP
IF some_condition THEN CONTINUE; END IF;
EXIT; -- intended to stop but CONTINUE was used
END LOOP;

Correct approach:

LOOP
IF some_condition THEN CONTINUE; END IF;
EXIT; -- EXIT stops the loop properly
END LOOP;

Root cause:

Confusing CONTINUE with EXIT leads to wrong loop control.

#3 FOR loop over unsupported data type causing error.

Wrong approach:

FOR c IN 'abc' LOOP
RAISE NOTICE '%', c;
END LOOP;

Correct approach:

FOR c IN SELECT unnest(string_to_array('abc', '')) LOOP
RAISE NOTICE '%', c;
END LOOP;

Root cause:

FOR loops require ranges or query results, not direct strings.

Key Takeaways

Loops in PostgreSQL automate repetitive tasks by repeating code blocks controlled by conditions or ranges.

LOOP runs indefinitely until EXIT stops it; WHILE checks a condition before each iteration; FOR loops iterate over ranges or query results.

EXIT and CONTINUE control loop flow precisely, preventing infinite loops and skipping iterations.

Nested loops multiply work and can cause performance issues; use set-based SQL when possible.

Advanced loop control with labels and exception handling improves robustness in complex functions.