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

Need a 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

Need a 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

Need a 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;

Need a hint?

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