Functions returning TABLE in PostgreSQL

Introduction

Functions returning TABLE let you get multiple rows and columns as a result. They help organize complex queries into reusable blocks.

When you want to return a set of rows with multiple columns from a function.

When you need to reuse a complex query logic multiple times.

When you want to simplify your main query by moving parts into functions.

When you want to return query results directly from a function without creating temporary tables.

Syntax

PostgreSQL

CREATE FUNCTION function_name(parameters) RETURNS TABLE(column1 datatype, column2 datatype, ...) AS $$
BEGIN
  RETURN QUERY
  SELECT ...;
END;
$$ LANGUAGE plpgsql;

The RETURNS TABLE clause defines the columns and their types the function will return.

Use RETURN QUERY to return the rows from a SELECT statement inside the function.

Examples

This function returns all users with their id and name.

PostgreSQL

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

This function returns orders for a specific customer.

PostgreSQL

CREATE FUNCTION get_orders_by_customer(cust_id INT) RETURNS TABLE(order_id INT, amount NUMERIC) AS $$
BEGIN
  RETURN QUERY SELECT id, total FROM orders WHERE customer_id = cust_id;
END;
$$ LANGUAGE plpgsql;

Sample Program

This example creates a table of employees, inserts some data, then defines a function that returns employees by department. Finally, it calls the function to get employees in the 'IT' department.

PostgreSQL

CREATE TABLE employees (
  emp_id SERIAL PRIMARY KEY,
  emp_name TEXT,
  department TEXT
);

INSERT INTO employees (emp_name, department) VALUES
('Alice', 'HR'),
('Bob', 'IT'),
('Carol', 'IT');

CREATE FUNCTION get_employees_by_dept(dept TEXT) RETURNS TABLE(emp_id INT, emp_name TEXT) AS $$
BEGIN
  RETURN QUERY SELECT emp_id, emp_name FROM employees WHERE department = dept;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_employees_by_dept('IT');

Important Notes

Functions returning TABLE are useful for returning multiple rows and columns without creating temporary tables.

Use RETURN QUERY inside the function to return the result of a SELECT statement.

You can call these functions in the FROM clause like a table.

Summary

Functions returning TABLE return multiple rows and columns as a result.

Define the output columns and types in RETURNS TABLE clause.

Use RETURN QUERY to return SELECT results inside the function.

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