Functions returning SETOF in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple SETOF function call

Consider the following PostgreSQL function that returns a set of integers. What will be the output of SELECT * FROM generate_numbers(3);?

PostgreSQL

CREATE OR REPLACE FUNCTION generate_numbers(n integer) RETURNS SETOF integer AS $$
BEGIN
  RETURN QUERY SELECT generate_series(1, n);
END;
$$ LANGUAGE plpgsql;

A

3
2
1

B

1
2
3

C 1, 2, 3

D Error: function does not return a set

💡 Hint

The function uses RETURN QUERY with generate_series from 1 to n.

The function returns a set of integers from 1 up to the input n using generate_series. The SELECT * FROM function returns each integer as a separate row.

📝 Syntax

intermediate

Identify the syntax error in a SETOF function

Which option contains a syntax error in defining a PostgreSQL function that returns SETOF text?

PostgreSQL

CREATE OR REPLACE FUNCTION list_names() RETURNS SETOF text AS $$
BEGIN
  RETURN QUERY SELECT name FROM users;
END;
$$ LANGUAGE plpgsql;

A CREATE OR REPLACE FUNCTION list_names() RETURNS SETOF text AS $$ BEGIN RETURN QUERY SELECT name FROM users; END; $$ LANGUAGE plpgsql;

B CREATE OR REPLACE FUNCTION list_names() RETURNS SETOF text AS $$ BEGIN RETURN NEXT SELECT name FROM users; END; $$ LANGUAGE plpgsql;

C ;lqsgplp EGAUGNAL $$ ;DNE ;sresu MORF eman TCELES YREUQ NRUTER NIGEB $$ SA txet FOTES SNRUTER )(seman_tsil NOITCNUF ECALPER RO ETAERC

D CREATE OR REPLACE FUNCTION list_names() RETURNS SETOF text AS $$ BEGIN RETURN SELECT name FROM users; END; $$ LANGUAGE plpgsql;

💡 Hint

RETURN QUERY is required to return multiple rows, RETURN alone returns a single value.

Option D uses RETURN instead of RETURN QUERY, which causes a syntax error because RETURN expects a single value, not a query returning multiple rows.

❓ optimization

advanced

Optimizing a SETOF function for performance

You have a function that returns SETOF rows from a large table using a loop and RETURN NEXT. Which option is the best way to optimize it?

PostgreSQL

CREATE OR REPLACE FUNCTION get_active_users() RETURNS SETOF users AS $$
DECLARE
  rec users%ROWTYPE;
BEGIN
  FOR rec IN SELECT * FROM users WHERE active = true LOOP
    RETURN NEXT rec;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

A Replace the loop with RETURN QUERY SELECT * FROM users WHERE active = true;

B Add an index on the active column and keep the loop as is.

C Change RETURNS SETOF users to RETURNS TABLE and keep the loop.

D Use a cursor to fetch rows and RETURN NEXT inside the loop.

💡 Hint

RETURN QUERY can return all rows at once without looping.

Using RETURN QUERY with a single SELECT is more efficient than looping with RETURN NEXT for large result sets.

🧠 Conceptual

advanced

Understanding the difference between RETURNS SETOF and RETURNS TABLE

Which statement correctly describes the difference between RETURNS SETOF and RETURNS TABLE in PostgreSQL functions?

A <code>RETURNS TABLE</code> defines named columns and allows direct SELECT from the function; <code>RETURNS SETOF</code> returns a set of a single type without named columns.

B <code>RETURNS SETOF</code> can only return scalar types; <code>RETURNS TABLE</code> can return composite types.

C <code>RETURNS TABLE</code> functions cannot be used in FROM clauses; <code>RETURNS SETOF</code> functions can.

D There is no difference; both are interchangeable in all cases.

💡 Hint

Think about how the output columns are defined and accessed.

RETURNS TABLE allows you to define column names and types directly in the function signature, making it easier to SELECT from the function with named columns. RETURNS SETOF returns a set of a single type, often a composite type or scalar, without explicit column names.

🔧 Debug

expert

Debugging a function returning SETOF with unexpected empty result

A function defined as below returns no rows when called, but the underlying SELECT query returns rows when run alone. What is the most likely cause?

PostgreSQL

CREATE OR REPLACE FUNCTION get_recent_orders() RETURNS SETOF orders AS $$
BEGIN
  RETURN QUERY SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '7 days';
  RETURN NEXT NULL;
END;
$$ LANGUAGE plpgsql;

A The function should use <code>RETURN NEXT</code> inside a loop instead of RETURN QUERY.

B The function is missing a <code>RETURN;</code> statement at the end.

C The extra <code>RETURN NEXT NULL;</code> causes the function to return no rows.

D The function's language should be SQL, not plpgsql.

💡 Hint

Consider how RETURN NEXT NULL affects the result set.

RETURN NEXT NULL adds a NULL row to the result set, but since it is after RETURN QUERY, it can cause confusion or unexpected results. In this case, it effectively overrides the previous rows, resulting in an empty or NULL-only result.

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