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.

Practice

1. What does a PostgreSQL function declared with RETURNS TABLE do?

easy

A. Returns no result, only performs actions

B. Returns only a single scalar value

C. Returns multiple rows and columns as a table result

D. Returns a JSON object

Solution

1. Step 1: Understand the purpose of RETURNS TABLE

   The RETURNS TABLE clause defines that the function will return a set of rows with specified columns, like a table.

2. Step 2: Compare with other return types

   Unlike scalar returns or void, RETURNS TABLE returns multiple rows and columns.

3. Final Answer:

   Returns multiple rows and columns as a table result -> Option C

4. Quick Check:

   RETURNS TABLE means multiple rows/columns [OK]

Hint: RETURNS TABLE means function outputs rows and columns [OK]

Common Mistakes:

• Thinking RETURNS TABLE returns a single value
• Confusing RETURNS TABLE with RETURNS VOID
• Assuming it returns JSON automatically

2. Which of the following is the correct syntax to declare a PostgreSQL function returning a table with columns id INT and name TEXT?

easy

A. CREATE FUNCTION f() RETURNS TABLE(id TEXT, name INT) AS $$ BEGIN RETURN QUERY SELECT 1, 'a'; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION f() RETURNS SETOF RECORD AS $$ BEGIN RETURN QUERY SELECT 1, 'a'; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION f() RETURNS INT AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION f() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT 1, 'a'; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Check RETURNS TABLE syntax

   CREATE FUNCTION f() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT 1, 'a'; END; $$ LANGUAGE plpgsql; correctly declares RETURNS TABLE(id INT, name TEXT) matching the column names and types.

2. Step 2: Verify RETURN QUERY usage

   CREATE FUNCTION f() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT 1, 'a'; END; $$ LANGUAGE plpgsql; uses RETURN QUERY SELECT 1, 'a'; which returns rows matching the table structure.

3. Final Answer:

   Correct RETURNS TABLE syntax and return statement -> Option D

4. Quick Check:

   RETURNS TABLE with matching columns and RETURN QUERY [OK]

Hint: RETURNS TABLE needs column names/types and RETURN QUERY [OK]

Common Mistakes:

• Using RETURNS SETOF RECORD without column definition
• Swapping column types in RETURNS TABLE
• Returning scalar instead of query

3. Given this function:

CREATE FUNCTION get_numbers() RETURNS TABLE(num INT) AS $$ BEGIN RETURN QUERY SELECT generate_series(1,3); END; $$ LANGUAGE plpgsql;

What will be the output of SELECT * FROM get_numbers();?

medium

A. 3 rows with values 1, 2, 3

B. 1 row with value 3

C. Error: function does not return a table

D. Empty result set

Solution

1. Step 1: Understand generate_series(1,3)

   This function generates rows with values 1, 2, and 3.

2. Step 2: RETURN QUERY returns all rows

   The function returns all rows from generate_series(1,3) as a table with column num.

3. Final Answer:

   3 rows with values 1, 2, 3 -> Option A

4. Quick Check:

   generate_series(1,3) returns 3 rows [OK]

Hint: generate_series returns multiple rows, so function returns them all [OK]

Common Mistakes:

• Thinking it returns a single row
• Confusing RETURNS TABLE with scalar return
• Expecting an error due to missing RETURNS SETOF

4. Identify the error in this function:

CREATE FUNCTION get_data() RETURNS TABLE(id INT, val TEXT) AS $$ BEGIN RETURN SELECT 1, 'a'; END; $$ LANGUAGE plpgsql;

medium

A. Missing RETURN QUERY before SELECT

B. Wrong column types in RETURNS TABLE

C. Function must return VOID

D. Missing LANGUAGE declaration

Solution

1. Step 1: Check RETURN statement in RETURNS TABLE function

   In PL/pgSQL, to return rows from a query, use RETURN QUERY, not just RETURN.

2. Step 2: Identify missing RETURN QUERY

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

3. Final Answer:

   Missing RETURN QUERY before SELECT -> Option A

4. Quick Check:

   Use RETURN QUERY to return rows in RETURNS TABLE [OK]

Hint: Use RETURN QUERY, not RETURN, to return table rows [OK]

Common Mistakes:

• Using RETURN instead of RETURN QUERY for table results
• Omitting LANGUAGE plpgsql
• Incorrect RETURNS TABLE column types

5. You want to create a function that returns a table of user IDs and their uppercase names from a users table with columns id INT and name TEXT. Which function definition correctly achieves this?

hard

A. CREATE FUNCTION get_upper_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, LOWER(name) FROM users; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION get_upper_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION get_upper_users() RETURNS SETOF record AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION get_upper_users() RETURNS TABLE(id TEXT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Match RETURNS TABLE columns with SELECT output

   The function returns id INT and name TEXT, so the SELECT must output these types. CREATE FUNCTION get_upper_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql; matches this.

2. Step 2: Check the transformation applied

   CREATE FUNCTION get_upper_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql; uses UPPER(name) to convert names to uppercase as required.

3. Step 3: Verify other options

   CREATE FUNCTION get_upper_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, LOWER(name) FROM users; END; $$ LANGUAGE plpgsql; uses LOWER instead of UPPER, CREATE FUNCTION get_upper_users() RETURNS SETOF record AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql; returns SETOF record (no column definition), CREATE FUNCTION get_upper_users() RETURNS TABLE(id TEXT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql; mismatches id type (TEXT instead of INT).

4. Final Answer:

   Correctly returns id and uppercase name as a table -> Option B

5. Quick Check:

   RETURNS TABLE with matching columns and UPPER(name) [OK]

Hint: Match RETURNS TABLE columns and use RETURN QUERY with correct SELECT [OK]

Common Mistakes:

• Using LOWER instead of UPPER
• Mismatching column types in RETURNS TABLE
• Using SETOF record instead of RETURNS TABLE