PL/pgSQL Table-Returning Functions — RETURNS TABLE, SETOF and When a View Is Better

Published Jun 25, 2026
by

A view is the most elegant way to encapsulate a recurring query — right up to the moment the query needs an argument from outside. A view has no parameters. As soon as a value belongs in the WHERE that the caller supplies, the table-valued function is the tool of choice: a function that returns an entire result set instead of a single value and can be called like a table.

This is exactly the line where it’s decided whether a read access should be a view, a function or a procedure. And it decides whether the query planner can see through it — or stands in front of a black box.

The essentials up front:

  • The boundary: a view when the set needs no parameters — a function as soon as it needs parameters.
  • The forms: RETURNS TABLE(...), RETURNS SETOF <type> and OUT parameters — which when.
  • The call: in the FROM clause like a table, with LATERAL even row by row.
  • The performance point: the planner can inline a LANGUAGE sql function into the query like a view (under conditions), while plpgsql stays a black box whose body it can’t integrate.

Prerequisite: PostgreSQL 12+ and PL/pgSQL basics. The example schema is called app, the function names follow the fn_<verb>_<name> convention.

Contents

When you actually need a table-valued function — and not a view

Four read/write needs, four tools. The dividing line between view and function is the question of parameters:

NeedTool
Return a single valuescalar function
Read a set — without parametersview
Read a set — with parameterstable-valued function
Write or orchestrateprocedure

A view is a stored query with no input. It’s ideal for “always give me the same view of the data” — say, a fixed revenue aggregation:

  1: CREATE VIEW app.vw_order_totals AS
  2:    SELECT
  3:        o.customer_id
  4:       ,count(*)      AS order_count
  5:       ,sum(o.total)  AS total_spent
  6:    FROM
  7:       app.orders o
  8:    GROUP BY
  9:       o.customer_id;

But if the caller wants to filter — “the orders of this customer”, “the revenue of this region” — then a value has to go in from outside. A view can’t do that; that’s the job of the table-valued function, and that step is the rest of this article. Everything else (a single value, a write) belongs to the other two tools in the table and isn’t the topic here.

RETURNS TABLE, SETOF and OUT parameters

Postgres has three ways to write a function that returns a set. The most common and most readable is RETURNS TABLE(...) — the return structure sits right in the signature:

  1: CREATE OR REPLACE FUNCTION app.fn_get_orders_by_customer
  2: (
  3:     p_customer_id  bigint
  4: )
  5: RETURNS TABLE
  6: (
  7:     order_id     bigint
  8:    ,ordered_at   timestamptz
  9:    ,total        numeric(12, 2)
 10: )
 11: LANGUAGE plpgsql
 12: STABLE
 13: AS $function$
 14: BEGIN
 15:    RETURN QUERY
 16:       SELECT
 17:           o.order_id
 18:          ,o.ordered_at
 19:          ,o.total
 20:       FROM
 21:          app.orders o
 22:       WHERE
 23:          o.customer_id = p_customer_id;
 24: END;
 25: $function$;

RETURN QUERY pushes the result of a query out as the return set; several RETURN QUERY in a row append their sets. (There’s also the row-by-row RETURN NEXT — it’s meant for the rare case where you compute each row individually, and is almost always slower than a RETURN QUERY.)

The two alternatives briefly:

  • RETURNS SETOF <type> returns whole rows of an existing type — usually a table: RETURNS SETOF app.orders. Handy when the function delivers exactly a table’s columns; you don’t repeat the column list.
  • OUT parameters are the third form: (IN p_customer_id bigint, OUT order_id bigint, OUT total numeric) implicitly yields a set. Functionally equivalent to RETURNS TABLE, but less obvious — RETURNS TABLE is the self-explanatory choice.

A trap with RETURNS TABLE + plpgsql: the column names from the TABLE(...) clause become variables in the function body. If a table column has the same name (order_id), an unqualified order_id becomes ambiguous and Postgres aborts. So qualify the table columns consistently with an alias (o.order_id) in the body — as in lines 17–23 above.

Calling it: FROM and LATERAL

A table-valued function goes where a table is allowed — in the FROM clause:

  1: -- Call it like a table
  2: SELECT * FROM app.fn_get_orders_by_customer(1);
  3:
  4: -- LATERAL: call the function per row with a row-dependent argument
  5: SELECT
  6:     c.customer_id
  7:    ,o.order_id
  8:    ,o.total
  9: FROM
 10:    app.customer c
 11:    CROSS JOIN LATERAL app.fn_get_orders_by_customer(c.customer_id) o
 12: WHERE
 13:    c.region = 'EU';

The first call is trivial. The second is the real reason functions are more powerful than views: LATERAL lets you pass the function an argument from the previous row (c.customer_id). So the query calls the function once per customer and joins the results — something a parameterless view can’t do.

Inlining: why the language choice matters

This is the performance core, and it hangs on a single line: LANGUAGE sql or LANGUAGE plpgsql. Write the same logic as a pure SQL function and the planner can inline it — it replaces the function call with its body and optimizes the whole query as if a view were sitting there (under conditions, more on that shortly). The plpgsql variant, by contrast, stays a black box: the planner can’t integrate its body into the surrounding query — it knows only the declared properties (cost, ROWS, volatility), not the contents.

The same query as LANGUAGE sql:

  1: CREATE OR REPLACE FUNCTION app.fn_get_orders_by_customer_sql
  2: (
  3:     p_customer_id  bigint
  4: )
  5: RETURNS TABLE
  6: (
  7:     order_id     bigint
  8:    ,ordered_at   timestamptz
  9:    ,total        numeric(12, 2)
 10: )
 11: LANGUAGE sql
 12: STABLE
 13: AS $function$
 14:    SELECT
 15:        o.order_id
 16:       ,o.ordered_at
 17:       ,o.total
 18:    FROM
 19:       app.orders o
 20:    WHERE
 21:       o.customer_id = p_customer_id;
 22: $function$;

The proof is in the execution plan. The LANGUAGE sql function disappears — the planner scans the table directly, filter and all:

EXPLAIN SELECT * FROM app.fn_get_orders_by_customer_sql(1);

 Seq Scan on orders o  (cost=0.00..25.00 rows=6 width=48)
   Filter: (customer_id = '1'::bigint)

The plpgsql function, on the other hand, shows up as its own Function Scan — and with a flat estimate of 1000 rows, because the planner doesn’t know the real set:

EXPLAIN SELECT * FROM app.fn_get_orders_by_customer(1);

 Function Scan on fn_get_orders_by_customer  (cost=0.25..10.25 rows=1000 width=48)

That 1000 is the default as long as no explicit ROWS <n> is given in the signature. If the planner estimates 1000 rows where 2 actually come (or vice versa), bad join orders and wrong strategies follow. For pure read sets, LANGUAGE sql is therefore almost always the better choice — inlinable, transparent, no estimation risk.

But inlining doesn’t happen automatically just because it says LANGUAGE sql — it’s tied to conditions: the function must be a single SELECT, declared STABLE or IMMUTABLE (a VOLATILE function is not inlined), and without SECURITY DEFINER. The STABLE in the example is thus not cosmetic but a precondition: it makes the function inlinable in the first place — and at the same time lets the planner reorder it freely.

Don’t take it as absolute: plpgsql is needed as soon as real procedural logic comes into play — loops, conditions, several statements building on each other, error handling. Then the black box is the price of the expressiveness, and you help the planner with a realistic ROWS <n> in the signature. The rule of thumb: a single SELECT → LANGUAGE sql; real logic → plpgsql.

SQL Server: inline vs. multi-statement TVF

If you come from the SQL Server world, you already know the distinction — it just goes by a different name there. SQL Server separates inline table-valued functions (a single RETURN (SELECT …)) from multi-statement TVFs (with a @table variable filled row by row). The parallel is almost one to one:

  • inline TVF ↔ LANGUAGE sql — single-statement, transparent to the optimizer, inlinable.
  • multi-statement TVF ↔ plpgsql — procedural, a black box with an estimate.

The shared lesson across both engines: let the optimizer see through it wherever you can. Multi-statement TVFs were long considered a performance trap in SQL Server (until inlining caught up in SQL Server 2019) — for exactly the same reason a plpgsql table function in Postgres gets an estimate instead of real statistics.

Project note: why this convention set prefers views

A brief step out of the general Postgres material — the conventions perspective this article comes from.

In the rule set this article series is about (the AI-assisted SQL development with Claude Code), table-valued functions deliberately barely appear: parameterless read sets there go through views as a rule (vw_<name>, read-only, idempotent), not through functions.

That doesn’t contradict the article — it’s its point in one sentence: the view is the first choice as long as no parameters are needed (inlinable, idempotent, fully transparent); the table-valued function is the tool for exactly the rest. Whoever reaches for the function first where a view would have done gives up transparency for nothing. How such conventions arise and get enforced is shown in the methodology article on deriving SQL conventions.

FAQ

RETURNS TABLE or SETOF — what’s the difference? RETURNS TABLE(...) defines the return columns right in the signature — readable and independent of existing types. RETURNS SETOF <type> returns whole rows of an existing type, usually a table (SETOF app.orders) — compact when the function delivers exactly its columns. They overlap functionally; RETURNS TABLE is the self-explanatory default.

Why is my plpgsql table function slower than a view? Because the planner can’t see into it. A plpgsql function shows up in the plan as a Function Scan with a flat estimate of 1000 rows; it can’t be optimized together with the rest of the query. A view — or a LANGUAGE sql function — is inlined and fully optimized instead. For pure reads, LANGUAGE sql or a view is almost always faster.

Can a view take parameters? No, not in the real sense. That’s exactly the boundary where the table-valued function begins. (There are workarounds via current_setting() or session variables, but that’s a hack, not clean parameterization — if you need parameters, use a function.)

How does this map to SQL Server TVFs? An inline TVF in SQL Server corresponds to a LANGUAGE sql function in Postgres (single-statement, inlinable), a multi-statement TVF to a plpgsql function (procedural, opaque with an estimate). The performance lesson is the same in both engines: prefer the form the optimizer can see through.

This article is a reference spoke of the cluster AI-assisted SQL development with Claude Code: