LOOP, WHILE, FOR iterations in PostgreSQL - Time & Space Complexity

Time Complexity: LOOP, WHILE, FOR iterations

O(n)

Understanding Time Complexity

When we use loops like LOOP, WHILE, or FOR in PostgreSQL, the time it takes to run depends on how many times the loop runs.

We want to understand how the total work grows as the number of loop cycles increases.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

DO $$
DECLARE
  counter INTEGER := 1;
BEGIN
  WHILE counter <= 1000 LOOP
    -- some simple operation
    counter := counter + 1;
  END LOOP;
END $$;
    

This code runs a WHILE loop that repeats a simple operation 1000 times.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The loop body runs once each time the counter increases.
• How many times: The loop runs exactly 1000 times in this example.

How Execution Grows With Input

As the number of loop cycles grows, the total work grows in the same way.

Input Size (n)	Approx. Operations
10	10
100	100
1000	1000

Pattern observation: If you double the number of loop cycles, the total work doubles too.

Final Time Complexity

Time Complexity: O(n)

This means the time to finish grows directly in proportion to how many times the loop runs.

Common Mistake

[X] Wrong: "The loop always takes the same time no matter how many times it runs."

[OK] Correct: Each loop cycle adds more work, so more cycles mean more total time.

Interview Connect

Understanding how loops affect time helps you explain how your code scales and shows you can think about efficiency clearly.

Self-Check

"What if we added a nested loop inside the existing loop? How would the time complexity change?"

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