输出多列的 PostgreSQL 函数或存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5462959/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL function or stored procedure that outputs multiple columns?
提问by A Question Asker
Here is what I ideally want. Imagine that I have a table with the row A.
这是我理想中想要的。想象一下，我有一个包含A行的表。
I want to do:
我想要做：
SELECT A, func(A) FROM table
and for the output to have say 4 columns.
并且输出有 4 列。
Is there any way to do this? I have seen things on custom types or whatever that let you sort of get a result that would look like
有没有办法做到这一点？我已经看到有关自定义类型或任何可以让您获得看起来像的结果的东西
A,(B,C,D)
A B C D）
But it would be really great if I could have that one function return multiple columnswithout any more finagling.
但是，如果我可以让一个函数返回多个列而无需再费力，那就太好了。
Is there anything that can do something like this?
有什么可以做这样的事情吗？
回答by bambam
If the function funcreturns only 1 row with 3 values, such as:
如果函数func仅返回具有 3 个值的 1 行，例如：
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;
and you then execute SELECT a, func(a) FROM table1you'll get:
然后你执行SELECT a, func(a) FROM table1你会得到：
a | func
integer | record
========|==========
1 | (2, 3, 4)
2 | (3, 4, 5)
3 | (4, 5, 6)
but, if you execute:
但是，如果您执行：
SELECT a, (f).output_val1, (f).output_val2, (f).output_val3
FROM (SELECT a, func(a) AS f FROM table1) AS x
you'll get:
你会得到：
a | output_val1 | output_val2 | output_val3
integer | integer | integer | integer
========|=============|=============|=============
1 | 2 | 3 | 4
2 | 3 | 4 | 5
3 | 4 | 5 | 6
or, using CTE(Common Table Expressions), if you execute:
或者，使用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
you'll also get:
您还将获得：
a | output_val1 | output_val2 | output_val3
integer | integer | integer | integer
========|=============|=============|=============
1 | 2 | 3 | 4
2 | 3 | 4 | 5
3 | 4 | 5 | 6
Note: you may also use the following queries to obtain the same results:
注意：您也可以使用以下查询来获得相同的结果：
SELECT a, (f).*
FROM (SELECT a, func(a) AS f FROM table1) AS x
or
或者
WITH temp AS (SELECT a, func(a) AS f FROM table1)
SELECT a, (f).* FROM temp
回答by ClubCranium
I agree with bambam's answer but would like to point out that HymanPDouglas's more succinct syntax SELECT a, (func(a)).* FROM table1, from my tests, would actually execute the function once for each column returned whereas the CTE expression will only execute the function once. So the CTE expression is preferred if the function takes a long time to execute.
我同意 bambam 的回答，但想指出 HymanPDouglas 更简洁的语法SELECT a, (func(a)).* FROM table1，从我的测试来看，实际上会为返回的每一列执行一次函数，而 CTE 表达式只会执行一次函数。因此，如果函数需要很长时间来执行，则首选 CTE 表达式。
回答by rudi-moore
If the function always returns 3 columns, you can do something like that:
如果函数总是返回 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;
If you can access the table from within a view, maybe you can create a view in some way
如果您可以从视图中访问该表，也许您可以通过某种方式创建一个视图
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);
then it's just a SELECT * FROM v. But again this looks like Inheritancecould be used.
那么它只是一个SELECT * FROM v. 但同样，这看起来可以使用继承。
回答by pyrocumulus
I think you will want to return a single record, with multiple columns? In that case you can use the return-type RECORDfor example. This will allow you to return an anonymous variable with as many columns as you want. You can find more information about all the different variables here:
我想你会想要返回一个多列的记录？在这种情况下，您可以使用 return-typeRECORD例如。这将允许您返回一个包含任意多列的匿名变量。您可以在此处找到有关所有不同变量的更多信息：
http://www.postgresql.org/docs/9.0/static/plpgsql-declarations.html
http://www.postgresql.org/docs/9.0/static/plpgsql-declarations.html
And about return types:
关于返回类型：
http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS
http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS
If you want to return multiple records with multiple columns, first check and see if you have to use a stored procedure for this. It might be an option to just use a VIEW(and query it with a WHERE-clause) instead. If that's not a good option, there is the possibility of returning a TABLEfrom a stored procedure in version 9.0.
如果要返回多列的多条记录，请首先检查并查看是否必须为此使用存储过程。可以选择只使用 a VIEW（并使用 WHERE 子句查询它）。如果这不是一个好的选择，则有可能TABLE从 9.0 版的存储过程中返回 a 。
