PostgreSQL Tutorial
Data Types
Querying & Filtering Data
Managing Tables
Modifying Data
Conditionals
Control Flow
Transactions & Constraints
Working with JOINS & Schemas
Roles & Permissions
Working with Sets
Subquery & CTEs
User-defined Functions
Important In-Built Functions
PostgreSQL PL/pgSQL
Variables & Constants
Stored Procedures
Working with Triggers
Working with Views & Indexes
Errors & Exception Handling
In PostgreSQL, you can create functions that return tables. This allows you to encapsulate a query inside a function and return results as if you were querying a table.
Here's how you can create and use a function that returns a table:
To define a function that returns a table, you use the RETURNS TABLE construct followed by the column definitions:
CREATE OR REPLACE FUNCTION function_name(parameters...)
RETURNS TABLE (column1 type1, column2 type2, ...)
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY
-- Your SQL query here
END;
$$;
Let's assume you have a table named employees:
CREATE TABLE employees (
id serial PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
birth_date DATE
);
Now, let's create a function that returns employees older than a specified age:
CREATE OR REPLACE FUNCTION employees_older_than(age_years INT)
RETURNS TABLE (id INT, full_name TEXT, age INT)
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY
SELECT id, first_name || ' ' || last_name AS full_name,
EXTRACT(YEAR FROM age(birth_date))::INT
FROM employees
WHERE EXTRACT(YEAR FROM age(birth_date)) > age_years;
END;
$$;
In the function above:
id, full_name, and age.EXTRACT function combined with the age function to calculate the age of each employee.age_years parameter.To use this function:
SELECT * FROM employees_older_than(30);
This will return all employees older than 30 years, with their id, full name, and age.
Functions that return tables can be especially useful to encapsulate complex queries or to provide a parameterized view-like functionality.
PostgreSQL function returning table example:
CREATE OR REPLACE FUNCTION get_employee_data()
RETURNS TABLE (employee_id INTEGER, employee_name TEXT, salary NUMERIC)
AS $$
BEGIN
RETURN QUERY SELECT id, name, salary FROM employees;
END;
$$ LANGUAGE plpgsql;
How to create a table-returning function in PostgreSQL:
RETURNS TABLE syntax.CREATE OR REPLACE FUNCTION get_product_prices()
RETURNS TABLE (product_id INTEGER, product_name TEXT, price NUMERIC)
AS $$
BEGIN
RETURN QUERY SELECT id, name, price FROM products;
END;
$$ LANGUAGE plpgsql;
PostgreSQL function returning multiple rows and columns:
RETURNS TABLE syntax.CREATE OR REPLACE FUNCTION get_sales_data()
RETURNS TABLE (order_id INTEGER, product_name TEXT, quantity INTEGER, total_price NUMERIC)
AS $$
BEGIN
RETURN QUERY SELECT id, product_name, quantity, total_price FROM sales;
END;
$$ LANGUAGE plpgsql;
Using SETOF in PostgreSQL table-returning functions:
SETOF keyword for functions returning a set of records.CREATE OR REPLACE FUNCTION get_orders_by_customer(customer_id INTEGER)
RETURNS SETOF orders
AS $$
BEGIN
RETURN QUERY SELECT * FROM orders WHERE customer_id = $1;
END;
$$ LANGUAGE plpgsql;
Defining a composite type for a table-returning function in PostgreSQL:
CREATE TYPE employee_info AS (employee_id INTEGER, employee_name TEXT, salary NUMERIC);
CREATE OR REPLACE FUNCTION get_employee_data()
RETURNS SETOF employee_info
AS $$
BEGIN
RETURN QUERY SELECT id, name, salary FROM employees;
END;
$$ LANGUAGE plpgsql;
Calling a table-returning function in PostgreSQL:
SELECT * FROM get_employee_data();
Passing parameters to a table-returning function in PostgreSQL:
CREATE OR REPLACE FUNCTION get_employees_by_department(department_id INTEGER)
RETURNS TABLE (employee_id INTEGER, employee_name TEXT)
AS $$
BEGIN
RETURN QUERY SELECT id, name FROM employees WHERE department_id = $1;
END;
$$ LANGUAGE plpgsql;
Dynamic SQL in table-returning functions in PostgreSQL:
CREATE OR REPLACE FUNCTION get_dynamic_data(query TEXT)
RETURNS TABLE (column1 INTEGER, column2 TEXT)
AS $$
BEGIN
RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql;
Limiting rows and columns in a table-returning function in PostgreSQL:
CREATE OR REPLACE FUNCTION get_top_employees(limit_count INTEGER)
RETURNS TABLE (employee_id INTEGER, employee_name TEXT)
AS $$
BEGIN
RETURN QUERY SELECT id, name FROM employees ORDER BY salary DESC LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;
Error handling in table-returning functions in PostgreSQL:
CREATE OR REPLACE FUNCTION get_safe_data()
RETURNS TABLE (column1 INTEGER, column2 TEXT)
AS $$
BEGIN
BEGIN
-- Query with potential error
RETURN QUERY SELECT 1 / 0;
EXCEPTION
WHEN OTHERS THEN
-- Handle the error
RETURN QUERY SELECT NULL, 'Error occurred';
END;
END;
$$ LANGUAGE plpgsql;
Documenting table-returning functions in PostgreSQL:
COMMENT ON FUNCTION get_employee_data() IS 'Returns employee information including ID, name, and salary.';
Using a table-returning function in a SELECT statement in PostgreSQL:
SELECT * FROM get_employee_data();
Table inheritance and table-returning functions in PostgreSQL:
CREATE OR REPLACE FUNCTION get_all_orders()
RETURNS TABLE (order_id INTEGER, order_date DATE, total_price NUMERIC)
AS $$
BEGIN
RETURN QUERY SELECT * FROM orders
UNION
SELECT * FROM archived_orders;
END;
$$ LANGUAGE plpgsql;
Working with the result set of a table-returning function in PostgreSQL:
SELECT * FROM get_employee_data() AS emp_data WHERE emp_data.salary > 50000;
Returning JSON or other data types from a table-returning function in PostgreSQL:
CREATE OR REPLACE FUNCTION get_employees_json()
RETURNS TABLE (employee_data JSON)
AS $$
BEGIN
RETURN QUERY SELECT jsonb_build_object('id', id, 'name', name, 'salary', salary)::JSON FROM employees;
END;
$$ LANGUAGE plpgsql;