Yes, a PostgreSQL function can return a table by using RETURNS TABLE (...) in its definition, which lets you return multiple rows and columns as if it were a query result. This is one of the most common ways to create set-returning functions in PostgreSQL.
What Does Returning a Table Mean?
In PostgreSQL, a function can return a single value (like an integer or text), a composite type, or a set of rows. When you use RETURNS TABLE, you define the exact column names and data types the function will output. The function then returns a set of rows that can be queried like a regular table, joined with other tables, or used in a FROM clause.
For example, a function that returns a table of employees with their department names would let you do SELECT * FROM get_employee_details(); and get back a result set with multiple columns and rows.
Basic Syntax and Example
The syntax for a function returning a table is straightforward:
CREATE OR REPLACE FUNCTION get_users(active_only boolean)
RETURNS TABLE (id integer, username text, email text)
AS $$
BEGIN
RETURN QUERY
SELECT id, username, email
FROM users
WHERE active = active_only;
END;
$$ LANGUAGE plpgsql;
In this example, the function takes a boolean parameter and returns a table with three columns: id, username, and email. The RETURN QUERY statement executes a query and returns its result set.
You can also use RETURN NEXT to return rows one at a time, which is useful when building rows incrementally:
CREATE FUNCTION get_numbers(limit int)
RETURNS TABLE (num integer)
AS $$
DECLARE i integer;
BEGIN
FOR i IN 1..limit LOOP
num := i;
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql;
This function returns a table with a single column num containing numbers from 1 to the limit.
Why Use RETURNS TABLE Instead of SETOF?
PostgreSQL offers two main ways to return a set of rows: RETURNS SETOF and RETURNS TABLE. The key difference is that RETURNS TABLE lets you define the output columns explicitly within the function, while RETURNS SETOF requires you to return a predefined composite type or an existing table’s row type.
- RETURNS TABLE is more flexible because you can create a function that returns columns that don’t exist as a single table or type.
- RETURNS SETOF is simpler when you want to return rows from an existing table or a custom type.
- Both can be used in
FROMclauses, butRETURNS TABLEoften makes the function more self-documenting.
For example, if you have a table employees and want a function that returns all columns, RETURNS SETOF employees is concise. But if you need to return only a few columns or computed values, RETURNS TABLE is clearer.
How to Call a Table-Returning Function
You call a table-returning function in the FROM clause of a query, just like a table. Here are common ways:
- Simple select:
SELECT * FROM get_users(true); - With column aliases:
SELECT u.id, u.username FROM get_users(false) AS u; - In a join:
SELECT * FROM orders o JOIN get_users(true) u ON o.user_id = u.id; - In a subquery:
SELECT * FROM (SELECT * FROM get_numbers(5)) AS nums;
You can also use LATERAL joins to call the function for each row of another table, which is powerful for correlated subqueries.
Common Pitfalls and Performance Tips
One common mistake is forgetting that the column names in RETURNS TABLE must match the column names in the RETURN QUERY result, or you’ll get an error. Also, be careful with data types: if the query returns an integer but you declared text, it will fail.
Performance-wise, table-returning functions can be slower than direct queries because they add a function call overhead. However, you can mitigate this by using LANGUAGE SQL instead of plpgsql for simple functions, as SQL functions can be inlined by the optimizer. For example:
CREATE FUNCTION get_active_users()
RETURNS TABLE (id integer, username text)
LANGUAGE SQL
AS $$
SELECT id, username FROM users WHERE active;
$$;
This SQL-language function can be inlined, meaning the optimizer can replace the function call with the underlying query, often improving performance.
Alternatives: Views and Materialized Views
If you just need to return a fixed query result, a view might be simpler than a function. Views are stored queries that can be queried like tables, but they don’t accept parameters. For parameterized results, you have a few options:
- Table-returning functions as described above.
- Prepared statements that can be reused with different parameters.
- Views with session variables (using
set_config), but this is hacky.
In practice, table-returning functions are the standard way to create parameterized views in PostgreSQL.
Frequently Asked Questions
Can a function return a table with dynamic columns? No, the column set must be fixed at function creation time. If you need dynamic columns, consider returning a JSON or hstore value instead.
What is the difference between RETURNS TABLE and RETURNS SETOF record? RETURNS SETOF record requires you to specify the column definition at call time using AS in the query, which is less convenient and error-prone. RETURNS TABLE defines columns once in the function, making it safer and easier to use.
Can I use a table-returning function in a WHERE clause? No, you cannot directly use a set-returning function in a WHERE clause. You must use it in the FROM clause or as a subquery, then filter the results.
How do I return an empty table from a function? Simply return no rows. In plpgsql, you can use RETURN QUERY SELECT ... WHERE false; or just RETURN; without any RETURN NEXT or RETURN QUERY.
Can a table-returning function be used in a view? Yes, you can create a view that selects from a table-returning function, but the function must be called with fixed arguments if you want the view to be stable.
Are table-returning functions safe from SQL injection? Yes, as long as you use parameterized queries inside the function (e.g., USING in EXECUTE or direct parameters in RETURN QUERY). Never concatenate user input into dynamic SQL without proper escaping.
Related
- Creating a Split Form in Access: Steps and Tips
- String Array Split in Java: Methods, Examples, and Tips
- SQL String Split: How to Separate Delimited Values
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MATCH Function: Syntax, Examples, and Common ErrorsIn Excel, MATCH is a function—not a separate statement—that returns the position of a value in a one-dimensional…
- Excel Test If Two Cells Are Equal: Formulas and FixesTo test whether two cells are equal in Excel, enter =A1=B1 in another cell, replacing A1 and B1…
- Show Errors as Blank in Excel: Formula and OptionsYes. In Excel for Microsoft 365, wrap a formula in IFERROR and return an empty text string when…
- Excel Formula for Checkbox: How to Use ItThe simplest Excel formula for a checkbox is =IF(A1=TRUE,"Checked","Unchecked"), assuming the checkbox is linked to cell A1. To…
- Excel Consolidate Text Data: Formulas and Power QueryTo consolidate text in Excel, join cells with TEXTJOIN or combine rows from multiple tables with Power Query.…
- Excel Text Filter Wildcards: How to Use ThemIn Excel for Microsoft 365 on Windows, text-filter wildcards let you match patterns: use * for any number…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.