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.