LOOP, WHILE, FOR iterations in PostgreSQL - Mini Project: Build & Apply

Using LOOP, WHILE, and FOR Iterations in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to practice using different types of loops in PostgreSQL to process book data step-by-step.

🎯 Goal: Create a PostgreSQL function that uses LOOP, WHILE, and FOR iterations to process book sales data and update a summary table.

📋 What You'll Learn

Create a table books with columns id, title, and sales.

Create a table sales_summary with columns total_books and total_sales.

Write a function process_sales() that uses a LOOP to iterate over book IDs.

Inside the function, use a WHILE loop to sum sales for books with sales less than a threshold.

Use a FOR loop to update the sales_summary table with total books and total sales.

💡 Why This Matters

🌍 Real World

Looping through data in database functions helps automate calculations and updates, like sales summaries in a bookstore.

💼 Career

Database developers and administrators often write stored procedures with loops to process data efficiently inside the database.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and sales (integer). Insert these exact rows: (1, 'Book A', 10), (2, 'Book B', 20), (3, 'Book C', 5).

PostgreSQL

-- Create the books table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the sales_summary table

Create a table called sales_summary with columns total_books (integer) and total_sales (integer). Insert one row with values 0 for both columns.

PostgreSQL

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, sales INTEGER);
INSERT INTO books (id, title, sales) VALUES
  (1, 'Book A', 10),
  (2, 'Book B', 20),
  (3, 'Book C', 5);
-- Create sales_summary table and insert initial row
-- Your code here

Hint

Define the sales_summary table and insert a starting row with zeros.

3

Write the process_sales() function with a LOOP and WHILE

Create a function called process_sales() in PL/pgSQL. Inside it, declare an integer variable book_id starting at 1. Use a LOOP to iterate while book_id is less than or equal to 3. Inside the loop, use a WHILE loop to sum sales of books with sales less than 15 into a variable small_sales_total. Increment book_id by 1 each iteration. End the loop after processing all books.

PostgreSQL

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, sales INTEGER);
INSERT INTO books (id, title, sales) VALUES
  (1, 'Book A', 10),
  (2, 'Book B', 20),
  (3, 'Book C', 5);

CREATE TABLE sales_summary (total_books INTEGER, total_sales INTEGER);
INSERT INTO sales_summary (total_books, total_sales) VALUES (0, 0);

-- Create function process_sales with LOOP and WHILE
-- Your code here

Hint

Use LOOP with EXIT WHEN to control the loop. Use WHILE inside to add sales less than 15.

4

Add a FOR loop to update sales_summary in the function

Inside the process_sales() function, after the LOOP, add a FOR loop that iterates over all rows in books. Use variables rec for each row. Inside the FOR loop, update sales_summary by setting total_books to the count of books and total_sales to the sum of all sales. End the function properly.

PostgreSQL

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, sales INTEGER);
INSERT INTO books (id, title, sales) VALUES
  (1, 'Book A', 10),
  (2, 'Book B', 20),
  (3, 'Book C', 5);

CREATE TABLE sales_summary (total_books INTEGER, total_sales INTEGER);
INSERT INTO sales_summary (total_books, total_sales) VALUES (0, 0);

CREATE OR REPLACE FUNCTION process_sales() RETURNS VOID AS $$
DECLARE
  book_id INTEGER := 1;
  small_sales_total INTEGER := 0;
  current_sales INTEGER;
BEGIN
  LOOP
    EXIT WHEN book_id > 3;
    SELECT sales INTO current_sales FROM books WHERE id = book_id;
    WHILE current_sales < 15 LOOP
      small_sales_total := small_sales_total + current_sales;
      EXIT; -- Exit WHILE after adding once to avoid infinite loop
    END LOOP;
    book_id := book_id + 1;
  END LOOP;

  -- Add FOR loop to update sales_summary
  -- Your code here
END;
$$ LANGUAGE plpgsql;

Hint

Use a FOR rec IN SELECT * FROM books LOOP to iterate all books. Update counters inside the loop. Then update the sales_summary table.

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