LOOP, WHILE, FOR iterations in PostgreSQL - Step-by-Step Execution

Concept Flow - LOOP, WHILE, FOR iterations

Start Loop

↓

Check Condition?

No→Exit Loop

Yes↓

Execute Loop Body

↓

Update Variables

↩Back to Check Condition

The loop starts, checks a condition, runs the body if true, updates variables, then repeats until the condition is false.

Execution Sample

PostgreSQL

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

This code runs a WHILE loop that prints the counter value from 1 to 3.

Execution Table

Step	counter	Condition (counter <= 3)	Action	Output
1	1	True	Print 'Counter: 1', counter = 2	Counter: 1
2	2	True	Print 'Counter: 2', counter = 3	Counter: 2
3	3	True	Print 'Counter: 3', counter = 4	Counter: 3
4	4	False	Exit loop

💡 counter becomes 4, condition 4 <= 3 is False, loop ends

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
counter	1	2	3	4	4

Key Moments - 2 Insights

Why does the loop stop when counter is 4 even though the loop body changes counter inside?

Because the condition is checked before each loop iteration (see execution_table step 4). When counter is 4, the condition counter <= 3 is false, so the loop exits before running the body again.

What happens if we forget to update the counter inside the loop?

The condition will always be true (if initially true), causing an infinite loop. Unlike the execution_table, it shows counter increasing each step to eventually stop the loop.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of counter at step 3?

A 2

B 3

C 4

D 1

💡 Hint

Check the 'counter' column in execution_table row with Step 3.

At which step does the loop condition become false?

A Step 4

B Step 2

C Step 3

D Step 1

💡 Hint

Look at the 'Condition (counter <= 3)' column in execution_table to find when it is False.

If we change the initial counter to 4, what happens to the loop execution?

A Loop runs once

B Loop runs three times

C Loop does not run at all

D Loop runs infinitely

💡 Hint

Refer to variable_tracker start value and condition check in execution_table step 1.

Concept Snapshot

LOOP, WHILE, FOR iterations in PostgreSQL:
- Use WHILE condition LOOP ... END LOOP to repeat while condition is true.
- Condition is checked before each iteration.
- Update variables inside loop to avoid infinite loops.
- Loop exits when condition becomes false.

Full Transcript

This example shows a WHILE loop in PostgreSQL that starts with counter = 1. Each loop iteration checks if counter is less than or equal to 3. If true, it prints the counter and increases it by 1. When counter reaches 4, the condition fails and the loop stops. The variable tracker shows how counter changes from 1 to 4. Key points include the condition check before the loop body and the importance of updating the counter to avoid infinite loops.