PL/pgSQL Functions Returning Rows

Master RETURNS SETOF for functions that return zero or more rows. Covers RETURN QUERY for set-based returns, RETURN NEXT for row-by-row construction, and the three call notations. Critical fix: original tutorial placed LANGUAGE PLPGSQL; (with terminating semicolon) before the AS $$ body, which prematurely ended the CREATE FUNCTION statement.

So far we've covered functions that return a single value. PostgreSQL functions can also return rows — one or many — using RETURNS SETOF. The result behaves like a virtual table that callers can SELECT FROM.

This page focuses on RETURNS SETOF — returning a set of rows that match an existing table or composite type's shape.

row_type is typically a table name — the function returns rows shaped like that table. The function body uses RETURN QUERY followed by a SELECT that produces the rows.

Three ways to call a SETOF-returning function:

DROP TABLE IF EXISTS film;

CREATE TABLE film (
    film_id      SERIAL PRIMARY KEY,
    title        VARCHAR(255),
    release_year INT
);

INSERT INTO film (title, release_year) VALUES
    ('Avengers: Endgame',     2019),
    ('Joker',                 2019),
    ('Parasite',              2019),
    ('1917',                  2019),
    ('Tenet',                 2020),
    ('Nomadland',             2020),
    ('Promising Young Woman', 2020);

Look up a film by ID. Even though the result is at most one row, returning SETOF film handles the "no match" case gracefully — zero rows is a valid set:

DROP FUNCTION IF EXISTS find_film_by_id(INT);

CREATE OR REPLACE FUNCTION find_film_by_id(p_id INT)
RETURNS SETOF film
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT * FROM film WHERE film_id = p_id;
END;
$$;

SELECT * FROM find_film_by_id(5);

Find all films from a given year:

DROP FUNCTION IF EXISTS films_in_year(INT);

CREATE OR REPLACE FUNCTION films_in_year(p_year INT)
RETURNS SETOF film
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT * FROM film
        WHERE  release_year = p_year
        ORDER  BY title;
END;
$$;

SELECT * FROM films_in_year(2020);

The same function can be called three ways, with different output formatting:

-- Style A: rows packed as composite values in one column
SELECT find_film_by_id(2);

-- Style B: extract one column from the row
SELECT (find_film_by_id(2)).title;

-- Style C: spread to columns (most common)
SELECT * FROM find_film_by_id(2);

The third form (SELECT * FROM) is what you'll use most — it integrates with the rest of SQL most naturally.

What happens when no rows match? SETOF handles this gracefully — you just get zero rows back:

SELECT * FROM find_film_by_id(9999);  -- no such film

This is one of the advantages of SETOF over a single-row return type — empty results are first-class, no exception handling needed.

Two ways to populate the result set:

RETURN NEXT shines when you want per-row processing — say, transforming each title to uppercase:

CREATE OR REPLACE FUNCTION films_uppercase(p_year INT)
RETURNS SETOF film
LANGUAGE plpgsql
AS $$
DECLARE
    rec film%ROWTYPE;
BEGIN
    FOR rec IN
        SELECT * FROM film WHERE release_year = p_year ORDER BY title
    LOOP
        rec.title := UPPER(rec.title);
        RETURN NEXT rec;
    END LOOP;
END;
$$;

SELECT * FROM films_uppercase(2019);

1. Default to RETURNS SETOF table_name for "find by criteria" functions. Empty result sets are graceful; no need to handle "no rows" specially.
2. Use RETURN QUERY when the body is a single SELECT. Cleanest, most efficient form.
3. Use RETURN NEXT only when row-by-row processing genuinely needs procedural logic. Otherwise the QUERY form is faster.
4. Add ORDER BY when row order matters. Without one, PostgreSQL doesn't promise an order.
5. Call with SELECT * FROM function(args) as the default style. Integrates naturally with the rest of SQL.
6. Place the LANGUAGE clause exactly once, after the closing $$. Don't mix it into the body or scatter it around.

• RETURNS SETOF row_type declares a function that returns zero or more rows shaped like the given type.
• Inside the body, use RETURN QUERY followed by a SELECT to produce the rows in one shot — the most common form.
• Use RETURN NEXT with a row variable to build the result set row-by-row inside a loop — useful for transformation.
• Three call styles: SELECT function(args), SELECT (function(args)).*, and SELECT * FROM function(args). The third is usually cleanest.
• Empty results are returned naturally — no exception handling needed for the "no match" case.