返回多个结果集的PostgreSQL函数

Question

在Postgres中，是否可以像MSSQL一样从函数返回多个结果集：

CREATE PROCEDURE test

AS

SELECT * FROM first_table

SELECT * FROM second_table

- Markus

Answer 1

从多个查询中返回单个结果集

自从PostgreSQL 8.3版本以来，有一种更简单的方法：

CREATE FUNCTION test()
  RETURNS SETOF first_table
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT * FROM first_table;

   RETURN QUERY
   SELECT * FROM second_table;  -- same row type as first_table!
END
$func$;

电话：

SELECT * FROM test();

两个结果集都会附加到从函数返回的单个集合中。当然，行类型必须匹配。
请参阅《RETURN QUERY》手册。

返回多个游标

你可以返回多个游标，这与你必须依次从每个游标中FETCH不完全相同。而且你必须在同一个事务中完成所有操作。手册中有一个代码示例。从函数中返回大量的行集可能会很有用。在大多数情况下，我宁愿依次运行每个SELECT语句。

- Erwin Brandstetter

---

不同的行类型有什么作用？除了游标外，是否有其他解决方案？我想要在一次调用中获取两个记录集。 - Udit Solanki

无法直接从函数中得到结果，但可以通过使用光标、临时表、json等文档类型来绕过此问题。我建议您提出一个新的问题，因为评论区不适合讨论此类问题。 - Erwin Brandstetter

Postgres11 有什么改进吗？ - Maulik Modi

@MaulikModi：这不能被“改进”，因为这是SQL语言固有的限制。考虑提出一个新问题，并详细说明您的情况。 - Erwin Brandstetter

@ErwinBrandstetter 其他数据库管理系统支持在存储过程中从查询批处理中返回多个异构结果集：它们返回“每个SELECT语句一个不同的结果集”。这不是SQL本身的限制，而是Postgres的限制，对吗？ - undefined

@戴：嗯，你说得对，原则上来说是这样的。SQL标准对函数的定义相当狭窄。 - undefined

Answer 2

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;

换句话说，使用 refcursors :)

- Frans Bouma

---

根据我的表格进行适配后，我得到以下查询语句： `SELECT [* FROM] "pr_GetCustomersAndOrders"(); pr_GetCustomersAndOrders

<未命名的 portal 11> <未命名的 portal 12>` 使用的是 psql 9.1 版本。 - valid

Answer 3

CREATE OR REPLACE FUNCTION public.TestReturnMultipleTales
( 
 param_coid integer, 
 ref1 refcursor,
 ref2 refcursor
)
RETURNS SETOF refcursor 
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000

AS $BODY$
DECLARE
            
BEGIN
  OPEN ref1 FOR SELECT * FROM dbo.tbl1 WHERE coid = param_coid;
  RETURN NEXT ref1;

  OPEN ref2 FOR SELECT * FROM dbo.tbl2 LIMIT 5;
  RETURN NEXT ref2;
END;
$BODY$;

USE IN pgSQL Query:- 

BEGIN;
    SELECT football_players.show_cities_multiple(123456, 'Ref1', 'Ref2');
    FETCH ALL IN "Ref1";
    FETCH ALL IN "Ref2";
COMMIT;

SELECT football_players.show_cities_multiple(123456, 'Ref1', 'Ref2');
FETCH ALL IN "Ref1";

SELECT football_players.show_cities_multiple(123456, 'Ref1', 'Ref2');
FETCH ALL IN "Ref2";

- Mayur Patel

Answer 4

如果first_table和second_table具有相同的布局，您也可以只使用

SELECT * FROM first_table WHERE ...
UNION ALL
SELECT * FROM second_table WHERE ...

[编辑：感谢评论者（名字可能不是“null”：））指出UNION ALL比UNION更快。]

- j_random_hacker

---

挑剔一些，但 UNION ALL 会更快（没有“| sort | uniq”），但如果有重复项则会返回重复项，不过这个影响较小。 - tommym

@null：好的，已更新。（我意识到你的名字可能不是“null”——似乎最近SO出了这个问题。我通过编辑我的个人资料页面上的顶部字段来解决它。） - j_random_hacker

是的，但是如果查询结果布局不一样怎么办？如果我们需要获取多个数据集，这种限制非常严格。我曾经在SQL Server中使用存储过程返回10个或更多的结果集。PostgreSQL中有类似的东西吗？ - MaxiWheat

@MaxiWheat：Frans Bouma的答案（返回REFCURSORs）覆盖了该情况，只要您在plpgsql中保持不变。http://www.postgresql.org/docs/8.4/interactive/plpgsql-cursors.html解释了如何将游标传回plpgsql之外（例如用于客户端库）。 - j_random_hacker

Answer 5

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)

当然，您可以使用现有的表/视图或自定义类型作为返回类型。

以下是使用 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)

请点击此处查看文档

- tommym

---

你只需要使用一个表格，即“customer”表。问题是关于多个结果集的！ - llouk