如何从PostgreSQL函数返回表的不确定数量列？

Question

众所周知，plpgsql函数可以返回如下表格：

RETURNS table(int, char(1), ...)

当创建函数时，列的列表是不确定的，那么如何编写此函数呢？

- Moon_of_father

Answer 1

当函数返回匿名记录时

RETURNS SETOF record

当使用SELECT * FROM语句时，你必须提供一列定义列表。SQL需要知道列名和类型才能解释*。对于注册的表和类型，系统目录提供了这些信息。对于函数，你需要自己声明。可以在函数定义或调用中声明。调用可能看起来像@Craig already provided。你可能没有仔细阅读他的答案。

根据你的具体需求，有多种方法可以解决这个问题：

1）返回一个匿名记录

示例：

CREATE OR REPLACE FUNCTION myfunc_single()  -- return a single anon rec
  RETURNS record AS
$func$
DECLARE
   rec record;
BEGIN
   SELECT into rec  1, 'foo';  -- note missing type for 'foo'
   RETURN rec;
END
$func$  LANGUAGE plpgsql;

这是一个非常有限的领域。只适用于使用以下函数定义的单个匿名记录：

RETURNS record

不使用 * FROM 的调用：

SELECT myfunc_single();

不适用于SRF（返回集合的函数），并且仅返回整个记录（类型为record）的字符串表示形式。很少有用。
要从单个匿名记录中获取单个列，需要再次提供列定义列表：

SELECT * FROM myfunc_single() AS (id int, txt unknown); -- note "unknown" type

2）以超集列返回众所周知的行类型

示例：

CREATE  TABLE t (id int, txt text, the_date date);
INSERT INTO t VALUES  (3, 'foz', '2014-01-13'), (4, 'baz', '2014-01-14');

CREATE OR REPLACE FUNCTION myfunc_tbl()  -- return well known table
  RETURNS SETOF t AS
$func$
BEGIN
   RETURN QUERY
   TABLE t;
   -- SELECT * FROM t;  -- equivalent
END
$func$  LANGUAGE plpgsql;

该函数返回表的所有列。只要您的表不包含大量列或巨大列，这种方法简短而简单，性能不会受到影响。

在调用时选择个别列：

SELECT id, txt FROM myfunc_tbl();
SELECT id, the_date FROM myfunc_tbl();

-> SQLfiddle展示了所有内容。

3) 高级解决方案

这个回答已经足够长了。而且这个相关的答案已经包含了所有内容：
重构一个PL/pgSQL函数以返回各种SELECT查询的输出

特别是看最后一章: 各种完整的表类型

- Erwin Brandstetter

Answer 2

如果返回结果的格式不确定/未定义，必须使用 RETURNS record 或 （对于多行结果）RETURNS SETOF record。

调用函数必须指定表格格式，例如：

SELECT my_func() AS result(a integer, b char(1));

顺便提一句，char是一种可怕的数据类型，其疯狂的空间填充规则可以追溯到固定宽度文件格式的时代。不要使用它。始终只使用text或varchar。

根据评论，让我们更加明确：

regress=> CREATE OR REPLACE FUNCTION f_something() RETURNS SETOF record AS $$
SELECT 1, 2, TEXT 'a';
$$ LANGUAGE SQL;
CREATE FUNCTION

regress=> SELECT * FROM f_something();
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM f_something();

regress=> SELECT * FROM f_something() AS x(a integer, b integer, c text);
 a | b | c 
---+---+---
 1 | 2 | a
(1 row)

- Craig Ringer

---

嗨。谢谢您的回复。如果您能给我提供一个示例代码，我将不胜感激。 - Moon_of_father

我尝试使用返回记录，但出现了这样的错误：ERROR：需要列定义列表以返回“record”的函数。 - Moon_of_father

@Moon_of_father，你读了我回答的第二部分吗？（编辑）更新为逐步示例。 - Craig Ringer