Functions returning SETOF in PostgreSQL - Mini Project: Build & Apply

Creating and Using PostgreSQL Functions Returning SETOF

📖 Scenario: You work at a bookstore database. You want to create a function that returns multiple rows of books based on a minimum price filter.

🎯 Goal: Build a PostgreSQL function that returns a set of rows (SETOF) from the books table filtered by a minimum price.

📋 What You'll Learn

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

Insert exactly three books with given titles and prices.

Create a function named get_books_above_price that takes a min_price parameter and returns SETOF books.

Use a RETURN QUERY statement inside the function to select books with price greater than or equal to min_price.

Call the function with a specific price to retrieve matching books.

💡 Why This Matters

🌍 Real World

Functions returning SETOF are useful to encapsulate complex queries that return multiple rows, making database code reusable and easier to maintain.

💼 Career

Database developers and backend engineers often write such functions to provide filtered or computed data sets to applications efficiently.

1

Create the books table and insert data

Create a table called books with columns id as integer primary key, title as text, and price as numeric. Then insert these three rows exactly: (1, 'The Hobbit', 15.99), (2, '1984', 12.50), (3, 'Clean Code', 33.00).

PostgreSQL

-- Create the books table and insert three rows
-- Your code here

Need a hint?

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

2

Define the function header with parameter and return type

Write the header of a PostgreSQL function named get_books_above_price that takes a parameter min_price of type numeric and returns SETOF books. Use LANGUAGE plpgsql and start the function body with BEGIN.

PostgreSQL

CREATE FUNCTION get_books_above_price(min_price NUMERIC) RETURNS SETOF books AS $$
BEGIN
  -- Your code here
END;
$$ LANGUAGE plpgsql;

Need a hint?

Use CREATE FUNCTION with the correct parameter and return type, and start the function with BEGIN.

3

Add the query to return books with price >= min_price

Inside the function body, add a RETURN QUERY statement that selects all columns from books where price >= min_price. Keep the BEGIN and END; keywords.

PostgreSQL

CREATE FUNCTION get_books_above_price(min_price NUMERIC) RETURNS SETOF books AS $$
BEGIN
  RETURN QUERY SELECT * FROM books WHERE price >= min_price;
END;
$$ LANGUAGE plpgsql;

Need a hint?

Use RETURN QUERY SELECT * FROM books WHERE price >= min_price; inside the function.

4

Call the function to get books priced 15 or more

Write a SQL query that calls the function get_books_above_price with the argument 15 to retrieve all books priced 15 or more.

PostgreSQL

-- Call the function get_books_above_price with 15
SELECT * FROM get_books_above_price(15);

Need a hint?

Use SELECT * FROM get_books_above_price(15); to call the function.