postgresql - 让返回类型成为 SETOF 表加上附加字段的简单方法？

Question

我正在编写一个 PL/pgSQL 存储过程，它将返回一组记录；每条记录包含现有表的所有字段(称之为零售商，它有两个字段:retailer_key 和 retailer_name)。这当然有效:

CREATE FUNCTION proc_Find_retailers
 (IN p_Store_key INT)
   RETURNS SETOF Retailer
   AS $$ ...`

现在我想更新 sp，以便它在每个返回记录的“末尾”返回另外两个字段。我可以做一些事情，例如:

CREATE FUNCTION proc_Find_store
 (IN p_Store_key INT)
   RETURNS TABLE (
      retailer_key int,
      retailer_name varchar(50),
      addl_field_1 int,
      addl_field_2 double precision)
   AS $$ ...

在现实世界中，我的 Retailer 表有 50 个字段(不是我示例中的两个)，因此在 RETURNS TABLE 子句中枚举所有这些字段是乏味的。这有什么捷径吗，所以我可能会说这样的话(我意识到我在这里编造的东西在语法上是非法的，但我这样做是为了让你了解我正在寻找的东西):

CREATE FUNCTION proc_Find_store
 (IN p_Store_key INT)
   RETURNS (SETOF Store,
      addl_field_1 int,
      addl_field_2 double precision)
   AS $$ ...

Answer 1

您可以将整行作为复合类型返回并添加更多:

CREATE OR REPLACE FUNCTION f_rowplus()
  RETURNS TABLE (rec demo, add_int int, add_txt text) AS
$func$
SELECT d, 5, 'baz'::text FROM demo d;
$func$  LANGUAGE sql;

但是，当您使用简单调用时:

SELECT * FROM f_rowplus();

您从表 demo 中获取行作为单独的复合类型。您必须调用:

SELECT (rec).*,  add_int, add_txt FROM f_rowplus();

获取所有个人列。需要括号。

Postgres 在这里有点不一致。如果您创建一个函数:

CREATE OR REPLACE FUNCTION f_row2()
  RETURNS TABLE (rec demo) AS
...

然后复合类型 demo 被静默地转换为单独的列(分解)。没有指向原始复合类型的链接。您根本无法引用声明的输出列 rec，因为它已被分解类型的列替换。此调用将导致错误消息:

<strike>SELECT rec FROM f_row2();</strike>  -- error!

这里也一样:

CREATE OR REPLACE FUNCTION f_row3(OUT rec demo)
  RETURNS SETOF demo AS
...

但是，一旦您添加任何 个OUT 列，复合类型将保留为已声明(未分解)，您可以:

SELECT rec FROM f_rowplus();

第一个函数。

db<> fiddle here - 展示所有变体
<子>旧sqlfiddle

旁白

当使用函数返回 FROM 列表中的多个列(作为表函数)并在 SELECT 列表中分解时，如下所示:

SELECT (rec).* FROM f_rowplus();

...该函数仍然仅被评估一次 - 同时在SELECT 列表中直接调用和 分解，如下所示:

SELECT (f_rowplus()).*;  -- also: different result

... 将为返回类型中的每一列计算一次。见:

• How to avoid multiple function evals with the (func()).* syntax in an SQL query?

在 Postgres 14 或更高版本中，您还可以使用标准 SQL 语法:

CREATE OR REPLACE FUNCTION f_rowplus_std()
  RETURNS TABLE (rec demo, add_int int, add_txt text)
  LANGUAGE sql PARALLEL SAFE
BEGIN ATOMIC
SELECT d, 5, 'baz'::text FROM demo d;
END;

参见:

• What does BEGIN ATOMIC ... END mean in a PostgreSQL SQL function / procedure?