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.

Practice

1. What does a LOOP statement do in PostgreSQL procedural code?

easy

A. Executes code only if a condition is true at the start.

B. Runs the code only once and then stops automatically.

C. Goes through each row of a query automatically.

D. Repeats the code inside it until an EXIT command is reached.

Solution

1. Step 1: Understand the LOOP statement

   A LOOP in PostgreSQL repeats the code inside it indefinitely until an EXIT command is used to stop it.

2. Step 2: Compare with other loops

   Unlike WHILE or FOR, LOOP does not check a condition automatically; it relies on EXIT to stop.

3. Final Answer:

   Repeats the code inside it until an EXIT command is reached. -> Option D

4. Quick Check:

   LOOP repeats until EXIT [OK]

Hint: LOOP runs forever until EXIT stops it [OK]

Common Mistakes:

• Thinking LOOP stops automatically without EXIT
• Confusing LOOP with WHILE or FOR
• Assuming LOOP checks conditions itself

2. Which of the following is the correct syntax to start a FOR loop iterating over rows from a query in PostgreSQL?

easy

A. FOR record FROM users LOOP

B. FOR (SELECT * FROM users) LOOP

C. FOR record IN SELECT * FROM users LOOP

D. FOR record WHILE SELECT * FROM users LOOP

Solution

1. Step 1: Recall FOR loop syntax for query iteration

   The correct syntax uses FOR record IN SELECT ... LOOP to iterate over query rows.

2. Step 2: Check each option

   FOR record IN SELECT * FROM users LOOP matches the correct syntax. Options B, C, and D are invalid or incomplete.

3. Final Answer:

   FOR record IN SELECT * FROM users LOOP -> Option C

4. Quick Check:

   FOR ... IN SELECT ... LOOP is correct syntax [OK]

Hint: FOR loops use 'IN SELECT' to iterate query rows [OK]

Common Mistakes:

• Omitting IN keyword
• Using parentheses incorrectly
• Mixing WHILE syntax with FOR

3. What will be the output of this PostgreSQL code snippet?

DECLARE
  counter INT := 1;
  total INT := 0;
BEGIN
  WHILE counter <= 3 LOOP
    total := total + counter;
    counter := counter + 1;
  END LOOP;
  RAISE NOTICE 'Total: %', total;
END;

medium

A. Syntax error

B. Total: 6

C. Total: 0

D. Total: 3

Solution

1. Step 1: Trace the WHILE loop iterations

   The loop runs while counter is 1, 2, and 3. Each time, total adds counter's value.

2. Step 2: Calculate total after loop

   Total = 0 + 1 + 2 + 3 = 6 after the loop ends.

3. Final Answer:

   Total: 6 -> Option B

4. Quick Check:

   Sum 1+2+3 = 6 [OK]

Hint: Add counter each loop until condition false [OK]

Common Mistakes:

• Stopping loop too early
• Adding counter after increment
• Confusing total initialization

4. Identify the error in this PostgreSQL loop code:

DECLARE
  i INT := 1;
BEGIN
  WHILE i < 5 LOOP
    RAISE NOTICE '%', i;
  END LOOP;
END;

medium

A. Missing increment of variable i inside the loop.

B. Incorrect declaration of variable i.

C. Syntax error in RAISE NOTICE statement.

D. WHILE condition should be i <= 5.

Solution

1. Step 1: Check loop control variable usage

   The variable i is never increased inside the loop, so the condition i < 5 never becomes false.

2. Step 2: Understand loop behavior

   Without increment, the loop runs forever causing an infinite loop error.

3. Final Answer:

   Missing increment of variable i inside the loop. -> Option A

4. Quick Check:

   Loop needs variable increment to stop [OK]

Hint: Always update loop variable to avoid infinite loops [OK]

Common Mistakes:

• Forgetting to increment loop variable
• Assuming loop stops automatically
• Misreading WHILE condition

5. You want to sum the price column from a products table using a FOR loop in PL/pgSQL. Which code snippet correctly does this?

hard

A. DECLARE total NUMERIC := 0; BEGIN FOR rec IN SELECT price FROM products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END;

B. DECLARE total NUMERIC := 0; BEGIN FOR rec products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END;

C. DECLARE total NUMERIC := 0; BEGIN FOR rec FROM SELECT price FROM products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END;

D. DECLARE total NUMERIC := 0; BEGIN WHILE rec IN SELECT price FROM products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END;

Solution

1. Step 1: Identify correct FOR loop syntax for query rows

   DECLARE total NUMERIC := 0; BEGIN FOR rec IN SELECT price FROM products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END; uses FOR rec IN SELECT price FROM products LOOP, which is correct syntax to iterate query results.

2. Step 2: Verify accumulation and output

   Inside the loop, it adds rec.price to total and then outputs total with RAISE NOTICE.

3. Final Answer:

   DECLARE total NUMERIC := 0; BEGIN FOR rec IN SELECT price FROM products LOOP total := total + rec.price; END LOOP; RAISE NOTICE '%', total; END; -> Option A

4. Quick Check:

   FOR ... IN SELECT ... LOOP sums prices [OK]

Hint: Use FOR ... IN SELECT ... LOOP to sum column values [OK]

Common Mistakes:

• Omitting IN keyword in FOR loop
• Using WHILE instead of FOR for query rows
• Incorrect loop variable or query syntax