Functions returning TABLE in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple TABLE function

Consider the following PostgreSQL function that returns a table of numbers and their squares.

What is the output of SELECT * FROM get_squares(3);?

PostgreSQL

CREATE OR REPLACE FUNCTION get_squares(n integer) RETURNS TABLE(num integer, square integer) AS $$
BEGIN
  RETURN QUERY SELECT i, i*i FROM generate_series(1, n) AS i;
END;
$$ LANGUAGE plpgsql;

A

num | square
----+--------
1   | 1
2   | 4
3   | 9

B

num | square
----+--------
1   | 1
2   | 2
3   | 3

C

num | square
----+--------
0   | 0
1   | 1
2   | 4
3   | 9

D

num | square
----+--------
1   | 1
4   | 16
9   | 81

💡 Hint

The function returns numbers from 1 to n and their squares.

The function uses generate_series(1, n) to produce numbers from 1 to n. For each number i, it returns i and i*i as columns num and square.

📝 Syntax

intermediate

Identify the syntax error in TABLE function

Which option contains the correct syntax to define a PostgreSQL function returning a table with columns id (integer) and name (text)?

A CREATE FUNCTION get_users() RETURNS TABLE(id integer, name text) AS $$ BEGIN RETURN QUERY SELECT user_id, user_name FROM users; END $$ LANGUAGE plpgsql;

B CREATE FUNCTION get_users() RETURNS TABLE(id integer, name text) AS $$ RETURN QUERY SELECT user_id, user_name FROM users; $$ LANGUAGE plpgsql;

C CREATE FUNCTION get_users() RETURNS TABLE(id integer, name text) AS $$ BEGIN RETURN QUERY SELECT user_id, user_name FROM users; END; $$ LANGUAGE plpgsql;

D CREATE FUNCTION get_users() RETURNS TABLE(id integer, name text) AS $$ BEGIN RETURN SELECT user_id, user_name FROM users; END; $$ LANGUAGE plpgsql;

💡 Hint

A TABLE function must use RETURN QUERY inside a BEGIN...END block.

Option C correctly uses BEGIN...END and RETURN QUERY. Option C misses BEGIN...END. Option C uses RETURN instead of RETURN QUERY. Option C misses semicolon after END.

❓ optimization

advanced

Optimizing a TABLE function with a loop

Given this function that returns numbers and their factorials, which option optimizes it best by avoiding explicit loops?

PostgreSQL

CREATE OR REPLACE FUNCTION factorials(n integer) RETURNS TABLE(num integer, fact bigint) AS $$
DECLARE
  f bigint := 1;
BEGIN
  FOR i IN 1..n LOOP
    f := f * i;
    RETURN NEXT (i, f);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

A CREATE OR REPLACE FUNCTION factorials(n integer) RETURNS TABLE(num integer, fact bigint) AS $$ BEGIN RETURN QUERY SELECT i, sum(i) FROM generate_series(1, n) AS i GROUP BY i; END; $$ LANGUAGE plpgsql;

B CREATE OR REPLACE FUNCTION factorials(n integer) RETURNS TABLE(num integer, fact bigint) AS $$ BEGIN RETURN QUERY SELECT i, factorial(i) FROM generate_series(1, n) AS i; END; $$ LANGUAGE plpgsql;

C CREATE OR REPLACE FUNCTION factorials(n integer) RETURNS TABLE(num integer, fact bigint) AS $$ BEGIN RETURN QUERY SELECT i, exp(sum(ln(i))) OVER (ORDER BY i) FROM generate_series(1, n) AS i; END; $$ LANGUAGE plpgsql;

D CREATE OR REPLACE FUNCTION factorials(n integer) RETURNS TABLE(num integer, fact bigint) AS $$ BEGIN RETURN QUERY SELECT i, i! FROM generate_series(1, n) AS i; END; $$ LANGUAGE plpgsql;

💡 Hint

PostgreSQL has a built-in factorial function.

Option B uses the built-in factorial function for each number, avoiding manual loops. Option B tries a complex window function but is incorrect. Option B sums numbers, not factorials. Option B uses invalid syntax i!.

🔧 Debug

advanced

Debugging a TABLE function with missing RETURN QUERY

What error will this function raise when called?

CREATE OR REPLACE FUNCTION get_data() RETURNS TABLE(id integer, val text) AS $$ BEGIN SELECT id, value FROM data_table; END; $$ LANGUAGE plpgsql;

A No error, returns all rows from data_table

B ERROR: syntax error at or near "SELECT"

C Function returns no rows

D ERROR: query has no destination for result data

💡 Hint

In PL/pgSQL, SELECT without INTO or RETURN QUERY causes error.

The function runs SELECT but does not specify where to send results. It must use RETURN QUERY to return rows in TABLE functions.

🧠 Conceptual

expert

Understanding TABLE function behavior with OUT parameters

Which statement about PostgreSQL functions returning TABLE with OUT parameters is true?

A Functions with OUT parameters automatically return a table without needing RETURN QUERY.

B Functions with OUT parameters must always use RETURN NEXT to return rows.

C Functions with OUT parameters cannot return multiple rows.

D Functions with OUT parameters require explicit RETURNS SETOF type instead of TABLE.

💡 Hint

OUT parameters define the output columns and the function returns rows automatically.

When a function declares OUT parameters, PostgreSQL treats it as returning a table. The function can assign values to OUT variables and use RETURN to return rows automatically.