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

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;

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;

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);

Hint

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

Practice

1.

What does a PostgreSQL function declared with RETURNS SETOF do?

easy

A. It returns multiple rows as a set of values.

B. It returns a single scalar value.

C. It returns a boolean indicating success or failure.

D. It returns a JSON object.

Solution

1. Step 1: Understand the meaning of RETURNS SETOF

   In PostgreSQL, RETURNS SETOF means the function returns multiple rows, not just one value.

2. Step 2: Compare with other return types

   Other return types like scalar or boolean return single values, not sets of rows.

3. Final Answer:

   It returns multiple rows as a set of values. -> Option A

4. Quick Check:

   RETURNS SETOF = multiple rows [OK]

Hint: SETOF means multiple rows, not one value [OK]

Common Mistakes:

• Thinking it returns a single value
• Confusing SETOF with JSON return
• Assuming it returns a boolean

2.

Which of the following is the correct syntax to declare a PostgreSQL function that returns a set of integers?

CREATE FUNCTION get_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN QUERY SELECT ...; END; $$ LANGUAGE plpgsql;

easy

A. CREATE FUNCTION get_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION get_numbers() RETURNS integer AS $$ BEGIN RETURN QUERY SELECT 1; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION get_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN QUERY SELECT 1; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION get_numbers() RETURNS TABLE(integer) AS $$ BEGIN RETURN QUERY SELECT 1; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Check the correct RETURNS clause

   To return multiple rows of integers, use RETURNS SETOF integer.

2. Step 2: Use RETURN QUERY for sets

   Inside the function, RETURN QUERY SELECT ... returns multiple rows properly.

3. Final Answer:

   CREATE FUNCTION get_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN QUERY SELECT 1; END; $$ LANGUAGE plpgsql; -> Option C

4. Quick Check:

   RETURNS SETOF + RETURN QUERY = CREATE FUNCTION get_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN QUERY SELECT 1; END; $$ LANGUAGE plpgsql; [OK]

Hint: Use RETURNS SETOF and RETURN QUERY for multiple rows [OK]

Common Mistakes:

• Using RETURNS integer instead of SETOF integer
• Using RETURN instead of RETURN QUERY for sets
• Confusing RETURNS TABLE with RETURNS SETOF

3.

Given this function:

CREATE FUNCTION get_even_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN QUERY SELECT generate_series(1,5) WHERE generate_series % 2 = 0; END; $$ LANGUAGE plpgsql;

What will SELECT * FROM get_even_numbers(); return?

medium

A. Empty set

B. [1, 3, 5]

C. [1, 2, 3, 4, 5]

D. [2, 4]

Solution

1. Step 1: Understand generate_series and filter

   The function selects numbers from 1 to 5 but filters only even numbers using WHERE generate_series % 2 = 0.

2. Step 2: Identify even numbers in range

   Even numbers between 1 and 5 are 2 and 4.

3. Final Answer:

   [2, 4] -> Option D

4. Quick Check:

   Even numbers 1-5 = [2,4] [OK]

Hint: Filter generate_series with modulo for evens [OK]

Common Mistakes:

• Including odd numbers by mistake
• Returning all numbers without filter
• Expecting empty set due to syntax confusion

4.

Identify the error in this function that returns a set of text values:

CREATE FUNCTION get_names() RETURNS SETOF text AS $$ BEGIN RETURN SELECT name FROM users; END; $$ LANGUAGE plpgsql;

medium

A. Missing RETURN QUERY before SELECT statement.

B. RETURNS SETOF text is invalid syntax.

C. Function must return TABLE, not SETOF.

D. LANGUAGE plpgsql is not allowed for set-returning functions.

Solution

1. Step 1: Check how to return multiple rows in plpgsql

   To return multiple rows, use RETURN QUERY SELECT ... inside the function.

2. Step 2: Identify missing keyword

   The function uses RETURN SELECT ... which is invalid; it must be RETURN QUERY SELECT ....

3. Final Answer:

   Missing RETURN QUERY before SELECT statement. -> Option A

4. Quick Check:

   Use RETURN QUERY for sets [OK]

Hint: Use RETURN QUERY to return sets inside plpgsql [OK]

Common Mistakes:

• Using RETURN instead of RETURN QUERY
• Confusing RETURNS SETOF with RETURNS TABLE
• Thinking LANGUAGE plpgsql disallows set returns

5.

You want to create a function that returns all employees with salary above a given amount. Which is the best way to write this function?

CREATE FUNCTION get_high_salary(min_salary numeric) RETURNS SETOF employees AS $$ BEGIN RETURN QUERY SELECT * FROM employees WHERE salary > min_salary; END; $$ LANGUAGE plpgsql;

What is the correct way to call this function to get all employees earning more than 50000?

hard

A. SELECT get_high_salary(50000);

B. SELECT * FROM get_high_salary(50000);

C. CALL get_high_salary(50000);

D. EXECUTE get_high_salary(50000);

Solution

1. Step 1: Understand function returns SETOF employees

   The function returns multiple rows, so it must be called in a FROM clause to get rows.

2. Step 2: Choose correct call syntax

   Use SELECT * FROM function_name(args); to get all rows returned by the function.

3. Final Answer:

   SELECT * FROM get_high_salary(50000); -> Option B

4. Quick Check:

   Call set-returning function in FROM clause [OK]

Hint: Call set-returning functions with SELECT * FROM [OK]

Common Mistakes:

• Using SELECT function() without FROM
• Using CALL which is for procedures
• Using EXECUTE which is for dynamic SQL