What's the difference between a function that returns TABLE
vs SETOF records
, all else equal.
CREATE FUNCTION events_by_type_1(text) RETURNS TABLE(id bigint, name text) AS $$
SELECT id, name FROM events WHERE type = $1;
$$ LANGUAGE SQL STABLE;
CREATE FUNCTION events_by_type_2(text) RETURNS SETOF record AS $$
SELECT id, name FROM events WHERE type = $1;
$$ LANGUAGE SQL STABLE;
These functions seem to return the same results. See this SQLFiddle.
When returning
SET OF record
the output columns are not typed and not named. Thus this form can't be used directly in a FROM clause as if it was a subquery or a table.That is, when issuing:
we get this error:
It can be "casted" into the correct column types by the SQL caller though. This form does work:
and results in:
For this reason
SET OF record
is considered less practical. It should be used only when the column types of the results are not known in advance.This answer is only to remember alternative context where TABLE and SETOF are equivalent.
As @a_horse_with_no_name pointed, it is not a RETURNS SETOF "unknown record", is a defined one.
In this example, the types
table
andsetof
are equivalent,The RETURNS SETOF have the advantage of reuse type (see footype), that is impossible with RETURNS TABLE.