PL/pgSQL Functions Returning Tables

Master RETURNS TABLE(col1 type, col2 type, ...) for returning rows with custom column shapes. Includes the implicit row-variable behavior of TABLE columns, RETURN NEXT vs RETURN QUERY, and column-name collision pitfalls. Critical fix: original tutorial had stray (100, 1000), garbage between LANGUAGE and AS that broke the CREATE FUNCTION.

RETURNS TABLE(...) is PostgreSQL's most flexible way to return rows from a function. Where RETURNS SETOF table_name ties the result to an existing table or composite type, RETURNS TABLE lets you define the result columns inline — any shape you want, including subsets, aggregates, and joins.

Two important rules:

• The SELECT in RETURN QUERY must produce columns whose types and order match the TABLE column list. Mismatches are caught at function creation time.
• Watch for naming collisions. The TABLE column names are also names in scope inside the function body. If a TABLE column has the same name as an actual table column, references inside the SELECT can become ambiguous.

DROP TABLE IF EXISTS film;

CREATE TABLE film (
    title        VARCHAR,
    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);

Return film titles matching a pattern, along with release years. Note the renamed result columns to avoid collision with the table's column names:

DROP FUNCTION IF EXISTS get_film(VARCHAR);

CREATE OR REPLACE FUNCTION get_film(p_pattern VARCHAR)
RETURNS TABLE(film_title VARCHAR, film_release_year INT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT title, release_year
        FROM   film
        WHERE  title ILIKE p_pattern
        ORDER  BY title;
END;
$$;

SELECT * FROM get_film('%er%');

Two films match: "Avergers: Endgame" and "Joker". The result columns are named film_title and film_release_year — distinct from the underlying table's title and release_year.

Filter by both pattern and year:

DROP FUNCTION IF EXISTS get_film(VARCHAR, INT);

CREATE OR REPLACE FUNCTION get_film(p_pattern VARCHAR, p_year INT)
RETURNS TABLE(film_title VARCHAR, film_release_year INT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT title, release_year
        FROM   film
        WHERE  title ILIKE p_pattern
          AND  release_year = p_year
        ORDER  BY title;
END;
$$;

SELECT * FROM get_film('%er%', 2019);

When you need to transform each row before returning it, use a FOR loop with RETURN NEXT. Each iteration appends one row to the result set:

DROP FUNCTION IF EXISTS get_film_upper(VARCHAR, INT);

CREATE OR REPLACE FUNCTION get_film_upper(p_pattern VARCHAR, p_year INT)
RETURNS TABLE(film_title VARCHAR, film_release_year INT)
LANGUAGE plpgsql
AS $$
DECLARE
    var_r record;
BEGIN
    FOR var_r IN
        SELECT title, release_year
        FROM   film
        WHERE  title ILIKE p_pattern
          AND  release_year = p_year
    LOOP
        film_title         := UPPER(var_r.title);
        film_release_year  := var_r.release_year;
        RETURN NEXT;
    END LOOP;
END;
$$;

SELECT * FROM get_film_upper('%er%', 2019);

RETURNS TABLE works for aggregates too — return a summary that doesn't match any single existing table:

CREATE OR REPLACE FUNCTION yearly_film_count()
RETURNS TABLE(year INT, film_count BIGINT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT release_year, COUNT(*)
        FROM   film
        GROUP  BY release_year
        ORDER  BY release_year;
END;
$$;

SELECT * FROM yearly_film_count();

Two columns, custom shape, neither matches any existing table — exactly the case where RETURNS TABLE beats RETURNS SETOF.

If a TABLE column has the same name as a column in your SELECT, references can become ambiguous. PostgreSQL will warn or error depending on context:

-- Trouble: TABLE columns named identically to film table columns
CREATE OR REPLACE FUNCTION get_film_bad(p_pattern VARCHAR)
RETURNS TABLE(title VARCHAR, release_year INT)   -- same names!
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT title, release_year FROM film WHERE title ILIKE p_pattern;
        --     ^^^^^                          ^^^^^
        --     ambiguous: which "title"? the TABLE col or the film col?
END;
$$;

Two ways to avoid the trouble:

1. Rename the TABLE columns to something distinct (film_title instead of title) — what we did in the earlier examples.
2. Qualify the SELECT references with the table name: SELECT film.title, film.release_year FROM film WHERE film.title ILIKE p_pattern.

1. Reach for RETURNS TABLE when the result shape is custom — subsets of columns, computed values, joined data, aggregates. Reach for RETURNS SETOF table_name when you're returning whole rows of an existing table.
2. Name TABLE columns distinctly from the underlying table's columns. film_title instead of title avoids ambiguity.
3. Use RETURN QUERY for the common case. Reach for RETURN NEXT in a loop only when per-row processing genuinely needs procedural logic.
4. Order results explicitly when callers might depend on row order — ORDER BY in the SELECT.
5. Match types carefully. If your TABLE declares release_year INT, the SELECT must produce an integer-compatible value — cast if needed: release_year::INT.
6. Don't put garbage between the LANGUAGE clause and AS. Sounds obvious but the original tutorial managed it — keep the function declaration clean.

• RETURNS TABLE(col1 type, col2 type, ...) defines a function that returns rows with a custom column shape.
• Use RETURN QUERY select_query when the body is a single SELECT — most common pattern.
• Use RETURN NEXT in a FOR loop when each row needs procedural transformation before being added to the result set.
• The TABLE column names also serve as variable names inside the function body — assign to them, then RETURN NEXT to emit a row.
• Name TABLE columns distinctly from the underlying table columns to avoid ambiguity.
• RETURNS TABLE is the most flexible row-returning form; reach for RETURNS SETOF table_name when returning whole rows of an existing table.