How to Return a Table from Function in PostgreSQL

In PostgreSQL, you can return a table from a function by using the RETURNS TABLE clause in the function signature. Inside the function, use RETURN QUERY to return rows matching the table structure.

Syntax

The basic syntax to create a function that returns a table in PostgreSQL is:

• RETURNS TABLE(column1 datatype, column2 datatype, ...): Defines the columns and their types the function will return.
• RETURN QUERY: Executes a query and returns its result as the function output.

sql

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

Example

This example creates a function that returns all employees with their id and name from a sample employees table.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT
);

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

CREATE OR REPLACE FUNCTION get_employees()
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name FROM employees;
END;
$$ LANGUAGE plpgsql;

-- Call the function
SELECT * FROM get_employees();

Output

 id | name 
----+-------
  1 | Alice
  2 | Bob
  3 | Carol
(3 rows)

Common Pitfalls

Common mistakes when returning tables from functions include:

• Not specifying RETURNS TABLE with correct column names and types.
• Using RETURN instead of RETURN QUERY to return rows.
• Mismatch between the query columns and the declared return columns.

Example of a wrong and right approach:

sql

-- Wrong: Missing RETURNS TABLE and using RETURN
CREATE FUNCTION wrong_func()
RETURNS SETOF RECORD AS $$
BEGIN
  RETURN QUERY SELECT id, name FROM employees;
END;
$$ LANGUAGE plpgsql;

-- Right: Using RETURNS TABLE and RETURN QUERY
CREATE FUNCTION right_func()
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY SELECT id, name FROM employees;
END;
$$ LANGUAGE plpgsql;

Quick Reference

Clause	Description
RETURNS TABLE(column1 datatype, ...)	Defines the output table structure of the function.
RETURN QUERY	Returns the result of a query as the function output.
LANGUAGE plpgsql	Specifies the procedural language used for the function.
SELECT * FROM function_name()	Calls the function and retrieves the returned table.

Key Takeaways

Use RETURNS TABLE with column names and types to define the output structure.

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

Ensure the SELECT columns match the declared return columns exactly.

Call the function with SELECT * FROM function_name() to get the table result.