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.