Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.8k views
in Technique[技术] by (71.8m points)

postgresql - SQL function return-type: TABLE vs SETOF records

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...