SQL函数返回类型：TABLE vs SETOF记录 [英] SQL function return-type: TABLE vs SETOF records

问题描述

返回 TABLE 与 SETOF记录的函数有什么区别，其他都相等。

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;

这些函数似乎返回相同的结果。请参见 SQLFiddle 。

These functions seem to return the same results. See this SQLFiddle.

推荐答案

返回 SETOF记录时，未键入和未命名输出列。因此，这种形式不能像在子查询或表中那样直接在FROM子句中使用。

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.

也就是说，在发出时：

SELECT * from events_by_type_2('social');

我们收到此错误：

错误：返回
record的函数需要列定义列表

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

但是，SQL调用程序会将其强制转换为正确的列类型。此表单可以正常工作：

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);

，结果为：

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

因此， SETOF记录为被认为不太实用。仅当预先不知道结果的列类型时，才应使用它。

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.

这篇关于SQL函数返回类型：TABLE vs SETOF记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！