SQL SETOF

What is SQL SETOF?

SETOF tells PostgreSQL that a function returns a set (zero or more rows) of the specified data type or composite type.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SETOF: PostgreSQL, Amazon Redshift, Greenplum, CockroachDB (experimental). Not available in MySQL, SQL Server, Oracle, or SQLite.

SQL SETOF Full Explanation

In PostgreSQL, functions can return either a single value or a collection of rows. The SETOF keyword, used in the RETURNS clause, designates the function as a set-returning function (SRF). When invoked in the FROM clause—behaving like a virtual table—or in a LATERAL join, the function can stream multiple rows to the calling query. SETOF may be paired with a built-in scalar type (e.g., integer, text), a user-defined composite type, or RECORD. When combined with OUT parameters, the engine automatically builds an anonymous composite row type. Rows are produced with RETURN NEXT or RETURN QUERY statements inside PL/pgSQL, or by simply selecting rows in SQL-language functions. Because the caller can receive 0, 1, or many rows, SRFs integrate naturally with set-based SQL operations such as JOINs, WHERE filters, and ORDER BY. Caveats: SRFs cannot be used directly in the SELECT list after PostgreSQL 10 unless marked as explicit lateral; SRFs executed per row in a SELECT list can cause performance surprises; and SRFs run inside simple SELECT statements must be placed in the FROM clause for clarity and future compatibility.

SQL SETOF Syntax

-- Define a set-returning function
CREATE FUNCTION get_active_users()
RETURNS SETOF users AS $$
    SELECT *
    FROM users
    WHERE active = true;
$$ LANGUAGE sql;

-- Call as a table
SELECT * FROM get_active_users();

SQL SETOF Parameters

Example Queries Using SQL SETOF

-- 1. Basic invocation
SELECT id, email FROM get_active_users();

-- 2. Using in a JOIN
SELECT o.order_id, u.email
FROM orders o
JOIN get_active_users() u ON u.id = o.user_id;

-- 3. Function returning SETOF RECORD
CREATE FUNCTION sales_by_month(month_start date)
RETURNS SETOF RECORD
AS $$
    SELECT product_id, SUM(amount) AS total
    FROM sales
    WHERE created_at >= month_start
      AND created_at <  month_start + INTERVAL '1 month'
    GROUP BY product_id;
$$ LANGUAGE sql;

SELECT *
FROM sales_by_month('2024-01-01') AS t(product_id int, total numeric);

Expected Output Using SQL SETOF

Each call returns zero or more rows that can be treated exactly like rows from a physical table, enabling further filtering, joining, and aggregation

Use Cases with SQL SETOF

Encapsulate complex business logic that outputs a table-shaped result
Replace database views where parameters are required
Stream large result sets from procedural code without building temporary tables
Provide polymorphic table functions that return different structures based on input parameters

Common Mistakes with SQL SETOF

Calling a SETOF function in the SELECT list instead of the FROM clause and getting unexpected behavior
Forgetting to alias column definitions when RETURNS SETOF RECORD is used, which leads to ambiguous column names
Assuming the function always returns at least one row, then joining with it and losing outer rows (should use LEFT JOIN if optional)
Omitting RETURN NEXT or RETURN QUERY inside PL/pgSQL, resulting in no rows returned

First Introduced In

PostgreSQL 7.3

Frequently Asked Questions

What is the difference between RETURNS SETOF and RETURNS TABLE?

RETURNS SETOF type tells PostgreSQL to stream rows of a single data type or composite type. RETURNS TABLE lets you declare column names and types inline, producing the same effect but with self-describing output.

How do I return multiple columns from a SETOF function?

Return a composite type: either reference an existing table type (e.g., RETURNS SETOF users) or declare OUT parameters/RECORD and provide a column list to the caller.

Can I filter a SETOF function just like a table?

Yes. Because the function appears in the FROM clause, you can apply WHERE, JOIN, GROUP BY, ORDER BY, LIMIT, and window functions exactly as you would on a real table.

Are SETOF functions cached like views?

No. Each invocation runs the function code. To avoid repeated work, materialize results into a table or use a VIEW if parameters are not required.