SQL function return-type: TABLE vs SETOF records

Question

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.

Answer 1

When returning SETOF 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:

SELECT * from events_by_type_2('social'); 

we get this error:

ERROR: a column definition list is required for functions returning "record"

It can be "casted" into the correct column types by the SQL caller though. This form does work:

SELECT * from events_by_type_2('social') as (id bigint, name text); 

and results in:

  id |      name       ----+----------------   1 | Dance Party   2 | Happy Hour  ... 

For this reason SETOF record is considered less practical. It should be used only when the column types of the results are not known in advance.