PostgreSQL函数或存储过程如何输出多列？

Question

这是我理想中的情况。假设我有一张包含行A的表格。

我想要做的是：

SELECT A, func(A) FROM table

有没有办法让输出具有4列？

有没有什么方法可以实现这一点？我看到过一些关于自定义类型或其他东西的内容，可以让你得到一个类似于

A，(B，C，D)

但是如果我可以让一个函数返回多个列而不需要进行额外的操作，那就太好了。

有没有什么可以做到这样的东西？

- A Question Asker

Answer 1

如果函数func仅返回一个包含三个值的行，如下：

CREATE OR REPLACE FUNCTION func
(
    input_val       integer,
    OUT output_val1 integer,
    OUT output_val2 integer,
    OUT output_val3 integer
)
AS $$
BEGIN
  output_val1 := input_val + 1;
  output_val2 := input_val + 2;
  output_val3 := input_val + 3;
END;
$$ LANGUAGE plpgsql;

如果你执行 SELECT a, func(a) FROM table1，你会得到：

a       | func
integer | record
========|==========
1       | (2, 3, 4)
2       | (3, 4, 5)
3       | (4, 5, 6)

但是，如果你执行：

SELECT a, (f).output_val1, (f).output_val2, (f).output_val3
FROM (SELECT a, func(a) AS f FROM table1) AS x

你将会获得：

a       | output_val1 | output_val2 | output_val3
integer | integer     | integer     | integer
========|=============|=============|=============
1       | 2           | 3           | 4
2       | 3           | 4           | 5
3       | 4           | 5           | 6

或者，使用CTE（公共表达式），如果执行以下操作：

WITH temp AS (SELECT a, func(a) AS f FROM table1)
SELECT a, (f).output_val1, (f).output_val2, (f).output_val3 FROM temp

您还将获得：

a       | output_val1 | output_val2 | output_val3
integer | integer     | integer     | integer
========|=============|=============|=============
1       | 2           | 3           | 4
2       | 3           | 4           | 5
3       | 4           | 5           | 6

注意：您也可以使用以下查询来获得相同的结果：

SELECT a, (f).*
FROM (SELECT a, func(a) AS f FROM table1) AS x

或者

WITH temp AS (SELECT a, func(a) AS f FROM table1)
SELECT a, (f).* FROM temp

- bambam

---

"我希望你能为我翻译以下内容：or SELECT a, (func(a)).* FROM table1. postgres 真是太棒了，我喜欢它。对于你我点赞+1，对于 postgres 我给100分。" - user533832

Answer 2

我同意bambam的答案，但想指出JackPDouglas更简洁的语法SELECT a, (func(a)).* FROM table1。根据我的测试，这个语法会为返回的每一列执行一次函数，而公共表达式语法只会执行一次函数。因此，如果函数需要很长时间才能执行，建议使用公共表达式语法。

- ClubCranium

---

虽然值得指出的是，如果函数是“IMMUTABLE”或“STABLE”，那么无论哪种方式都不会有太大影响。+1 - user533832

Answer 3

如果该函数始终返回3列数据，您可以这样做：

CREATE TYPE sometype AS (b INT, c TEXT, d TEXT);

CREATE OR REPLACE FUNCTION func(a TEXT) RETURNS SETOF sometype AS $$
BEGIN
  RETURN QUERY EXECUTE 'SELECT b, c, d FROM ' || a;
END;
$$ LANGUAGE plpgsql;

SELECT a, (f).b, (f).c, (f).d 
FROM (SELECT a, func(a) AS f FROM table) x;

---

如果您可以从视图中访问表，那么您可以通过某种方式创建视图

CREATE VIEW v AS 
SELECT 'tab1' AS a, b, c, d FROM tab1 WHERE 'tab1' IN (SELECT a FROM table)
UNION
SELECT 'tab2' AS a, b, c, d FROM tab2 WHERE 'tab2' IN (SELECT a FROM table)
UNION
SELECT 'tab3' AS a, b, c, d FROM tab3 WHERE 'tab3' IN (SELECT a FROM table);

那么这就只是一个 SELECT * FROM v。但是看起来似乎可以使用继承。

- rudi-moore

---

我对这个的问题在于它需要用户进行一些相当笨重的处理。我知道如果这是我唯一的选择，那么我就理解了，但我正在努力为用户创造更加优雅的体验，而不是更差的。理想情况下，我希望他们能够执行 SELECT myfunc(); 然后输出一个由我指定的完整表格，然后再根据需要进行一些标记等操作。也许 SQL 无法做到这一点？ - A Question Asker

你的用户直接执行 SQL 吗？我真的不理解这样做的目的。用户将输入表名和一些标志，对于每个表名，他们会得到不同数量和不同类型的列？那么他们会用这个做什么呢？他们还需要为每个表编写不同的语句吗？那么用户写 SELECT myfunc('tablename') 而不是写 SELECT * FROM tablename 有什么优势呢？或者你所有的表都有相似的表定义吗？那么你可能想要使用继承 http://www.postgresql.org/docs/9.0/interactive/ddl-inherit.html - rudi-moore

也许我评论得不太对。我的意思是，我想把你写的很酷的东西压缩成一行，形式为SELECT a，f（a）FROM table; 这样你就会得到4列。所以基本上和你的一样，只是压缩成了一行。虽然你的方法肯定很好，但我想知道为什么我们不能让它更简洁？ - A Question Asker

SELECT a, f(a) FROM table 只定义了两个结果列。所以你永远不会得到4列。你可以尝试这样做 SELECT a,b,c,d FROM f((SELECT a FROM v))，其中v是一个视图 CREATE VIEW v AS SELECT array(SELECT a FROM table) a。但不知道这是否更好，如果是这种情况，函数必须接受一个数组，为数组的每个成员执行一个查询，并返回所有输入值和3列 CREATE FUNCTION f(IN a_in TEXT[], OUT a TEXT, OUT b INT, OUT c TEXT, OUT d TEXT) RETURNS SETOF record AS $$ ..。 - rudi-moore

Answer 4

我想你希望返回一条记录，其中包含多个列？在这种情况下，你可以使用返回类型RECORD。这将允许你返回一个匿名变量，其中包含任意数量的列。你可以在这里找到有关所有不同变量的更多信息：

http://www.postgresql.org/docs/9.0/static/plpgsql-declarations.html

关于返回类型：

http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS

如果您想返回多个记录和多个列，首先请检查并确定是否需要使用存储过程。可能只需使用一个VIEW（并使用WHERE子句查询它）即可。如果这不是一个好的选择，那么在9.0版本中有从存储过程返回TABLE的可能性。

- pyrocumulus

---

我认为返回一个表可能是一个不错的选择，但考虑到我的使用情况，这有点尴尬。我的目标是允许用户执行像这样的操作：SELECT col_a，col_b，func（col_a）FROM a_table; 结果将是一个表：col_a，col_b，func_col_a，func_col_b，func_col_c。我基本上正在尝试将复杂的功能封装到函数中，而不会强制用户使用一堆非常丑陋的语法，就像下面的答案一样。这种解决方案之所以必要，原因是我经常需要使用LIMITs，所以嵌套的东西只会变得更糟。我想知道我想要的是否可行？ - A Question Asker