PostgreSQL 函数返回多个结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/756689/
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 returning multiple result sets
提问by Markus
Is it possible to return multiple result sets from a Postgres function, like in MSSQL:
是否可以从 Postgres 函数返回多个结果集，例如在 MSSQL 中：
CREATE PROCEDURE test
AS
SELECT * FROM first_table
SELECT * FROM second_table
回答by Erwin Brandstetter
A simpler way has been around since PostgreSQL 8.3:
自 PostgreSQL 8.3 以来，出现了一种更简单的方法：
CREATE FUNCTION test()
RETURNS SETOF first_table AS
$func$
BEGIN
RETURN QUERY
SELECT * FROM first_table;
RETURN QUERY
SELECT * FROM second_table; -- has to return same rowtype as first_table!
END
$func$ LANGUAGE plpgsql;
Call:
称呼：
SELECT * FROM test();
Both result sets are appended to a single set returned from the function.
See the manual for RETURN QUERY.
两个结果集都附加到从函数返回的单个集合中。
参见手册RETURN QUERY。
回答by Frans Bouma
CREATE OR REPLACE FUNCTION "pr_GetCustomersAndOrders"()
RETURNS SETOF refcursor AS
$BODY$DECLARE
customerRC refcursor;
orderRC refcursor;
BEGIN
open customerRC FOR
SELECT * FROM customers;
RETURN NEXT customerRC;
open orderRC FOR
SELECT * FROM orders;
RETURN NEXT orderRC;
RETURN;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION "pr_GetCustomersAndOrders"() OWNER TO postgres;
I.o.w. using refcursors :)
Iow 使用 refcursors :)
回答by j_random_hacker
If first_tableand second_tablehave the same layout, you can also just use
如果first_table和second_table有相同的布局，你也可以只使用
SELECT * FROM first_table WHERE ...
UNION ALL
SELECT * FROM second_table WHERE ...
[EDIT: Thanks to a commenter (whose name is probably not "null" :) ) for pointing out that UNION ALLis faster than UNION.]
[编辑：感谢评论者（他的名字可能不是“空”:)）指出它UNION ALL比UNION.]
回答by tommym
Yes.
是的。
Example:
例子：
test=# create function x () returns setof integer language plpgsql as $$ begin return next 1; return next 2; end $$;
CREATE FUNCTION
test=# select * from x();
x
---
1
2
(2 rows)
You can of course use an existing table/view or a custom type for the returned type.
您当然可以使用现有表/视图或自定义类型作为返回类型。
Example using language SQL:
使用语言 SQL 的示例：
test=# create table customer (name varchar, birth_date date);
CREATE TABLE
test=# create function y () returns setof customer language sql as $$
select * from customer
union all
select * from customer
$$;
CREATE FUNCTION
test=# insert into customer values ('joe', now()::date);
INSERT 0 1
test=# insert into customer values ('jill', now()::date);
INSERT 0 1
test=# select * from y();
name | birth_date
------+------------
joe | 2009-04-16
jill | 2009-04-16
joe | 2009-04-16
jill | 2009-04-16
(4 rows)
