FOREACH for array iteration in PostgreSQL - Mini Project: Build & Apply

Using FOREACH to Iterate Over Arrays in PostgreSQL

📖 Scenario: You are managing a small library database. Each book has a list of genres stored as an array. You want to process each genre for every book to prepare reports.

🎯 Goal: Build a PostgreSQL function that uses FOREACH to iterate over an array of genres for a book and insert each genre into a separate table for reporting.

📋 What You'll Learn

Create a table books with columns id (integer) and genres (text array).

Create a table book_genres with columns book_id (integer) and genre (text).

Write a function process_book_genres that takes a book id and uses FOREACH to iterate over the genres array of that book.

Inside the loop, insert each genre into the book_genres table with the corresponding book_id.

💡 Why This Matters

🌍 Real World

Many databases store lists or tags as arrays. Using FOREACH helps process each item individually for reporting or further analysis.

💼 Career

Database developers and administrators often write functions to manipulate array data efficiently using loops like FOREACH in PostgreSQL.

1

Create the books table with sample data

Create a table called books with columns id as integer and genres as text array. Insert one row with id 1 and genres array containing 'Fiction', 'Adventure', and 'Mystery'.

PostgreSQL

-- Create the books table and insert one row
-- Your code here

Hint

Use CREATE TABLE to define the table and ARRAY[...] to insert the array of genres.

2

Create the book_genres table

Create a table called book_genres with columns book_id as integer and genre as text.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    genres TEXT[]
);

INSERT INTO books (id, genres) VALUES (1, ARRAY['Fiction', 'Adventure', 'Mystery']);

-- Create the book_genres table
-- Your code here

Hint

Use CREATE TABLE with the specified columns.

3

Write the process_book_genres function with FOREACH loop

Write a PostgreSQL function called process_book_genres that takes an integer parameter p_book_id. Inside the function, declare a variable g of type text. Use FOREACH g IN ARRAY to loop over the genres array from the books table for the given p_book_id. For now, just write the loop structure without inserting data.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    genres TEXT[]
);

INSERT INTO books (id, genres) VALUES (1, ARRAY['Fiction', 'Adventure', 'Mystery']);

CREATE TABLE book_genres (
    book_id INTEGER,
    genre TEXT
);

-- Write the process_book_genres function with FOREACH loop
-- Your code here

Hint

Declare an array variable genres_arr TEXT[], use SELECT genres INTO genres_arr FROM books WHERE id = p_book_id, then FOREACH g IN ARRAY genres_arr LOOP.

4

Complete the function by inserting each genre into book_genres

Inside the FOREACH loop in the process_book_genres function, insert each genre g along with p_book_id into the book_genres table.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    genres TEXT[]
);

INSERT INTO books (id, genres) VALUES (1, ARRAY['Fiction', 'Adventure', 'Mystery']);

CREATE TABLE book_genres (
    book_id INTEGER,
    genre TEXT
);

CREATE OR REPLACE FUNCTION process_book_genres(p_book_id INTEGER) RETURNS VOID AS $$
DECLARE
    genres_arr TEXT[];
    g TEXT;
BEGIN
    SELECT genres INTO genres_arr FROM books WHERE id = p_book_id;
    FOREACH g IN ARRAY genres_arr LOOP
        -- Insert each genre into book_genres
        -- Your code here
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Hint

Use INSERT INTO book_genres (book_id, genre) VALUES (p_book_id, g); inside the loop.

Practice

1. What is the main purpose of the FOREACH statement in PostgreSQL when working with arrays?

easy

A. To convert an array into a string.

B. To loop through each element of an array easily inside PL/pgSQL.

C. To sort the elements of an array in ascending order.

D. To create a new array from existing tables.

Solution

1. Step 1: Understand FOREACH usage context

   FOREACH is used inside PL/pgSQL to iterate over array elements one by one.

2. Step 2: Identify the main purpose

   It simplifies looping through arrays without manual index handling.

3. Final Answer:

   To loop through each element of an array easily inside PL/pgSQL. -> Option B

4. Quick Check:

   FOREACH loops over array elements [OK]

Hint: FOREACH loops over array elements inside PL/pgSQL [OK]

Common Mistakes:

• Confusing FOREACH with array creation or sorting
• Thinking FOREACH works outside PL/pgSQL
• Assuming FOREACH converts arrays to strings

2. Which of the following is the correct syntax to iterate over an integer array arr using FOREACH in PL/pgSQL?

easy

A. FOREACH element IN ARRAY arr LOOP ... END LOOP;

B. FOREACH element FROM arr LOOP ... END LOOP;

C. FOREACH element ON arr LOOP ... END LOOP;

D. FOREACH element OVER arr LOOP ... END LOOP;

Solution

1. Step 1: Recall FOREACH syntax

   The correct syntax uses 'IN ARRAY' to specify the array to loop over.

2. Step 2: Match syntax options

   Only FOREACH element IN ARRAY arr LOOP ... END LOOP; uses 'IN ARRAY' correctly; others use invalid keywords.

3. Final Answer:

   FOREACH element IN ARRAY arr LOOP ... END LOOP; -> Option A

4. Quick Check:

   FOREACH ... IN ARRAY ... is correct syntax [OK]

Hint: Use 'IN ARRAY' to loop over arrays with FOREACH [OK]

Common Mistakes:

• Using FROM, ON, or OVER instead of IN ARRAY
• Omitting LOOP or END LOOP keywords
• Trying to use FOREACH outside PL/pgSQL

3. Consider the following PL/pgSQL block:

DECLARE
  arr integer[] := ARRAY[2, 4, 6];
  sum integer := 0;
BEGIN
  FOREACH val IN ARRAY arr LOOP
    sum := sum + val;
  END LOOP;
  RAISE NOTICE '%', sum;
END;

What will be the output when this block runs?

medium

A. Syntax error

B. 24

C. 12

D. 0

Solution

1. Step 1: Understand the loop iteration

   The FOREACH loops over arr elements: 2, 4, and 6.

2. Step 2: Calculate the sum

   sum starts at 0, then adds 2 + 4 + 6 = 12.

3. Final Answer:

   12 -> Option C

4. Quick Check:

   2 + 4 + 6 = 12 [OK]

Hint: Sum array elements by adding each in FOREACH loop [OK]

Common Mistakes:

• Assuming FOREACH does not add elements
• Confusing sum initialization
• Expecting syntax error due to RAISE NOTICE

4. Identify the error in this PL/pgSQL snippet:

DECLARE
  arr text[] := ARRAY['a', 'b', 'c'];
  ch text;
BEGIN
  FOREACH ch IN arr LOOP
    RAISE NOTICE '%', ch;
  END LOOP;
END;

medium

A. Variable 'ch' must be declared as integer, not text.

B. Array declaration syntax is incorrect.

C. RAISE NOTICE cannot print variables inside loops.

D. FOREACH must use 'IN ARRAY' before the array variable.

Solution

1. Step 1: Check FOREACH syntax

   FOREACH requires 'IN ARRAY' before the array variable to iterate properly.

2. Step 2: Identify the error

   The code uses 'IN arr' instead of 'IN ARRAY arr', causing syntax error.

3. Final Answer:

   FOREACH must use 'IN ARRAY' before the array variable. -> Option D

4. Quick Check:

   FOREACH ... IN ARRAY ... is correct syntax [OK]

Hint: Always write 'FOREACH var IN ARRAY arr' [OK]

Common Mistakes:

• Omitting 'ARRAY' keyword in FOREACH
• Wrong variable type for array elements
• Misunderstanding RAISE NOTICE usage

5. You want to write a PL/pgSQL function that takes an integer array and returns the count of even numbers using FOREACH. Which of the following code snippets correctly implements this?

hard

A. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOREACH val IN ARRAY arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

B. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOREACH val FROM arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

C. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOR val IN ARRAY arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

D. CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$
DECLARE
val integer;
count integer := 0;
BEGIN
FOREACH val ON ARRAY arr LOOP
IF val % 2 = 0 THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
$$ LANGUAGE plpgsql;

Solution

1. Step 1: Check FOREACH syntax correctness

   CREATE FUNCTION count_evens(arr integer[]) RETURNS integer AS $$ DECLARE val integer; count integer := 0; BEGIN FOREACH val IN ARRAY arr LOOP IF val % 2 = 0 THEN count := count + 1; END IF; END LOOP; RETURN count; END; $$ LANGUAGE plpgsql; uses 'FOREACH val IN ARRAY arr LOOP', which is the correct syntax.

2. Step 2: Verify logic for counting even numbers

   Inside the loop, it checks if val is even and increments count correctly.

3. Final Answer:

   FOREACH val IN ARRAY arr LOOP with IF val % 2 = 0 logic -> Option A

4. Quick Check:

   FOREACH ... IN ARRAY ... and correct IF condition [OK]

Hint: Use 'FOREACH val IN ARRAY arr' and check val % 2 = 0 [OK]

Common Mistakes:

• Using 'FROM' or 'ON ARRAY' instead of 'IN ARRAY'
• Confusing FOREACH with FOR loops
• Missing RETURN statement or wrong logic