从 PL/pgSQL 函数返回多行数据

来自 PostgreSQL wiki
跳转到导航跳转到搜索

作者：Stephan Szabo
最后更新时间：2003 年 4 月 4 日

PostgreSQL 7.3 现在支持一个更灵活的系统来编写返回集合的函数（SRF），这些函数与一些新的函数权限选项相结合，可以为设置模式提供更大的灵活性。我假设你已经具备一些在 PostgreSQL 中使用 SQL 和 PL/pgSQL 编写函数的经验。我们将使用一组非常简单的表和数据以及用 SQL 和 PL/pgSQL 编写的函数来进行演示。

create table department(id int primary key, name text);

create table employee(id int primary key, name text, salary int, departmentid int references department);

insert into department values (1, 'Management');
insert into department values (2, 'IT');

insert into employee values (1, 'John Smith', 30000, 1);
insert into employee values (2, 'Jane Doe', 50000, 1);
insert into employee values (3, 'Jack Jackson', 60000, 2);

SRF 可以返回由现有表定义的 rowtype 或泛型记录类型。首先，让我们看一个返回现有表 rowtype 的简单 SQL 函数。

create function GetEmployees() returns setof employee as 'select * from employee;' language 'sql';

这个非常简单的函数只是返回 employee 表中的所有行。让我们分解一下这个函数。该函数的返回值类型为 setof employee，这意味着它将返回一组 employee 行。函数的主体是一个非常简单的 SQL 语句，用于生成输出行。

SRF 可以代替查询 FROM 子句中的表或子查询使用。例如，要使用此函数获取 id 大于 2 的所有员工信息，你可以编写如下代码

select * from GetEmployees() where id > 2;

这很好，但如果你想返回更复杂的内容，例如部门列表以及该部门所有员工的总工资怎么办？如果你想返回现有的记录类型，你需要创建一个虚拟类型来保存输出类型，例如

create type holder as (departmentid int, totalsalary int8);

在这里，我们定义了一个名为 holder 的新类型，它是一个复合类型，包含一个名为 departmentid 的整数和一个名为 totalsalary 的大整数。然后我们可以定义返回此类型集的函数。对于此函数，我们将编写一个 SQL 版本，然后编写一个 PL/pgSQL 版本

create function SqlDepartmentSalaries() returns setof holder as
'
select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid
'
language 'sql';
create or replace function PLpgSQLDepartmentSalaries() returns setof holder as
'
declare
r holder%rowtype;
begin
for r in select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid loop
return next r;
end loop;
return;
end
'
language 'plpgsql';

SQL 与上面的 GetEmployee() 函数非常相似。它返回一个由类型 holder (int, int8) 定义的行集。它返回的行由主体中的 group by 查询定义。

PL/pgSQL 函数稍微复杂一点，但让我们逐步分析它。该函数首先声明一个名为 r 的变量，该变量的 rowtype 为 holder。此变量将用于存储来自主体中查询的行。主体对声明的 group by 查询进行循环，并将 r 设置为每个顺序的行。循环的主体是新的 return form，'return next'，这意味着一个输出行被排队到函数的返回集中。这不会导致函数返回。目前，返回 SRF 的 PL/pgSQL 函数必须在返回之前生成整个结果集，尽管如果集合变得很大，它将被写入磁盘。此限制可能会在未来版本中删除。

这些函数的使用方式与第一个函数相同，

select * from PLpgSQLDepartmentSalaries();

PL/pgSQL 函数还可以对记录进行其他操作或只排队一些记录。例如，如果你想了解部门的运营费用，其中部门的总工资大于 70,000 的部门的间接费用为 75%，其他部门的间接费用为 50%，并且只想返回工资加间接费用大于 100,000 的部门的部门 ID，你可以编写类似下面的代码

create or replace function ExpensiveDepartments() returns setof int as
'
declare
r holder%rowtype;
begin
for r in select departmentid, sum(salary) as totalsalary
from GetEmployees() group by departmentid loop

if (r.totalsalary > 70000) then
r.totalsalary := CAST(r.totalsalary * 1.75 as int8);
else
r.totalsalary := CAST(r.totalsalary * 1.5 as int8);
end if;

if (r.totalsalary > 100000) then
return next r.departmentid;
end if;

end loop;
return;
end
'
language 'plpgsql';

让我们看看它与 PLpgSQLDepartmentSales() 之间的区别。此函数返回一组整数（部门 ID）而不是一组复合类型，因为我们只需要返回昂贵部门的 ID。函数工作方式的主要变化在循环内部，所以让我们更仔细地看看。

if (r.totalsalary > 70000) then
r.totalsalary := CAST(r.totalsalary * 1.75 as int8);
else
r.totalsalary := CAST(r.totalsalary * 1.5 as int8);
end if;

在这里，我们计算了总工资加上间接费用，并相应地更新了记录。接下来，我们要确定总工资是否大于 100,000，如果是，则返回其标识符，因此

if (r.totalsalary > 100000) then
return next r.departmentid;
end if;

请注意，对于 return next，我们没有返回记录 r，而是只返回了 departmentid，因为此函数返回一组整数。如果我们想返回一个 holder 来包含工资加间接费用值，我们可以将函数定义为返回 setof holder 并在此使用 return next r;。

到目前为止，复合类型返回函数仅适用于你确定返回的类型与函数声明返回的类型具有相同的类型。如果你犯了错误，你会在 SQL 函数创建时收到错误，在 PL/pgSQL 函数执行时收到错误。但是，如果你只在执行时才知道函数需要返回的复合类型的详细信息怎么办？在这种情况下，你可以返回一个 setof record。这告诉 PostgreSQL 你希望函数返回一个复合类型，但你将在稍后告诉它期望什么类型。

让我们创建一个函数，该函数返回你作为参数传入的表的所有行。

create or replace function GetRows(text) returns setof record as
'
declare
r record;
begin
for r in EXECUTE ''select * from '' || $1 loop
return next r;
end loop;
return;
end
'
language 'plpgsql';

调用此函数比调用上面的 SRF 更复杂。我们需要向系统提供一些信息，说明我们期望此函数返回哪些类型，作为查询的一部分。PostgreSQL 对这些函数的处理方式与表子查询类似，并使用类似的语法来提供此信息，就像用于为子查询列提供别名一样。

select * from GetRows('Department') as dept(deptid int, deptname text);

在这里，我们传入 Department 作为参数，这意味着我们期望以 Department 记录的通用形式获得行，即一个整数后跟一个文本字符串，因此我们告诉 PostgreSQL 结果的别名应称为 dept，它由一个名为 deptid 的整数和一个名为 deptname 的文本组成。

最后，我们将制作 PL/pgSQL 函数，这些函数完全从头开始合成行。让我们做一些非常简单的事情，一个函数返回从 1 到参数的数字以及这些数字的两倍。第一个版本使用预定义类型作为其返回类型和内部类型。

create type numtype as (num int, doublenum int);

create or replace function GetNum(int) returns setof numtype as
'
declare
r numtype%rowtype;
i int;
begin
for i in 1 .. $1 loop
r.num := i;
r.doublenum := i*2;
return next r;
end loop;
return;
end
'
language 'plpgsql';

这很简单。该函数创建了一个 rowtype 为 numtype 的变量，对于从 1 到传入参数的每个数字，它都会分配 num 和 doublenum，然后执行 return next r; 为了将一个输出行排队。我们可以使用记录类型做同样的事情，这样我们就不需要外部类型，但是它要复杂得多，涉及一个虚假选择。

2003/01/13 08:19 EST（通过网络）：...仍然很希望看到如何使用 RECORD 类型来完成最后一个示例。

2003/01/13 13:43 EST（通过网络）：你需要做一些事情（假设 r 是记录类型，并且返回 setof record）：在 for 循环中使用 r 之前，执行 select into r 1::int as num, 1::int as doublenum;。这很丑陋，当我在 SFPUG 上进行讨论时，大家一致认为这是一个糟糕的技巧，并且几乎是解决问题的错误方法。:)

2003/01/14 01:25 EST（通过网络）：谢谢。请继续添加到本节中。其他文档对这个主题的描述非常薄弱。

WarMage 2003/01/28 08:04 EST（通过网络）：如果我创建一个插入某些内容但不返回任何内容（或成功/错误代码）的函数，我该如何处理？以下是我问题的示例
不返回函数（如果可能）

create function InsertEmployee(int,text,int,int) returns ??? as '
insert into employee values ($1, $2, $3, $4);
' language 'SQL'

状态返回函数

create function InsertEmployee(int,text,int,int) return int as '
insert into employee values ($1, $2, $3, $4);
//How do i capture any errors???
//if no errors return success [0] else error code or something
' language 'SQL'

任何帮助都将不胜感激，因为我仍然非常不熟悉 postgresql warmage@magicmail.co.za


justinc, 2003/01/28 16:35 EST（通过网络）：如果有人（除了我）有时间，点击此页面顶部的“编辑此页面”链接，修复评论并正确排列示例会非常棒。

2003/02/27 11:27 EST（通过网络）：致 WarMage：我相信在 7.3 中，如果你不想使用函数的值，可以使其返回 void。从技术上讲，我认为你仍然会得到一个包含 NULL 的结果集，但你无需使用最终的 select。至于状态返回，如果出现错误（除了尚未检查的外部键冲突 - 如延迟约束），当前语句将结束，因此它不会到达函数中的下一个语句。

2003/03/10 08:37 EST（通过网络）：可通过 PostgreSQL 邮件列表获得技术支持，邮件列表在此处提供

https://postgresql.ac.cn/community/lists

2003/03/14 18:39 EST（通过网络）：如果以错误的方式调用返回集的函数（即你通常调用函数的方式），你会收到此错误消息：在无法接受集的上下文中调用了返回值为集的函数。错误：select sr_func(arg1, arg2, ...); 正确：select * from sr_func(arg1, arg2, ...);

2003/03/29 13:52 EST（通过网络）：太棒了！这是一个非常好的帮助！

2003/04/01 18:21 EST（通过网络）：完美！现在，关于一些返回 C 语言中集的函数的示例呢？

2003/04/04 15:21 EST（通过网络）：对于 C 语言函数，我相信 contrib 中的 dblink 包含返回一组元组的 C 语言函数。

2003/04/17 03:39 EST（通过网络）：当尝试在 Select 查询中使用函数时，我遇到了问题：我得到> 错误执行查询 declare mycursor for select * from GetEmlpoyees() WHERE id > 2；PostgreSQL 错误消息：ERROR: parser parse error at or near "(" PostgreSQL 状态：PGRES_FATAL_ERROR 有人知道为什么我不能在查询中使用函数吗？

2003/04/17 05:51 EST（通过网络）：在此添加你的评论...

2003/04/17 05:53 EST（通过网络）：如何捕获 plpgsql 返回的系统错误？如果有人知道，请通过以下地址与我联系：nmogas@xlm.pt

2003/04/24 14:52 EST（通过网络）：这是一个非常重要的教程，因为很多人不知道如何创建这种类型的函数（过程），并且在 PostgreSQL 上创建这种类型函数的方式与其他 RDBMS（如 MSSQL、ORACLE、INFORMIX、INTERBASE/FIREBIRD 等）非常不同。此教程必须成为 Postgresql 函数文档的一部分，并提供更多其他语言的示例，如 Python、Perl、C 等，而不是 SQL 和 PL/PGSQL。

2003/04/24 16:44 EST（通过网络）：请注意，如果你没有为每个 return next 的返回类型填写所有值，旧值将被使用，因此你必须手动将它们设为 null。当对数据进行反规范化时，这会成为一个问题，因为反规范化过于复杂，无法通过 select 处理，因此必须使用嵌套的 'for select in' 循环进行处理。我遇到这种情况最常见的是创建不使用聚合函数的复杂透视表。我希望看到 'return next' 推送返回行，然后将所有列设置为 null，以便为新数据做好准备。以下简化的示例展示了我正在谈论的内容（我知道这可以通过子查询来完成，但在更复杂的情况下，它必须以迭代方式完成）

create type returntype as ( a int, b int, c_type1 varchar, c_type2 varchar, d_type1 varchar d_type2 varchar ); create function tst_func() returns setof returntype as ' declare r returntype%rowtype; rLoopA RECORD; rLoopB RECORD; begin for rLoopA IN select a, b from foo where argle loop r.a := rLoopA.a; r.b := rLoopA.b; for rLoopB IN select distinct on (foo, bar) foo, bar, data from sometable where bargle -- this select may return a row for for every column of returntype -- if a row/column is not returned, it should show null in the result set loop if foo = type1 and bar = c then r.c_type1 := rLoopB; else r.c_type1 := NULL; -- note the explicit set to null end if; if foo = type2 and bar = c then r.c_type2 := rLoopB; else r.c_type2 := NULL; end if; if foo = type1 and bar = d then r.d_type1 := rLoopB; else r.d_type1 := NULL; end if; if foo = type2 and bar = d then r.d_type2 := rLoopB; else r.d_type2 := NULL; end if; end loop; return next r; end loop; end; ' language 'plpgsql';

2003/04/24 16:48 EST（通过网络）：抱歉，忘记了我的代码周围的 pre /pre。以下是再次展示。以下简化的示例展示了我正在谈论的内容（我知道这可以通过子查询来完成，但在更复杂的情况下，它必须以迭代方式完成）

create type returntype as
(
a int,
b int,
c_type1 varchar,
c_type2 varchar,
d_type1 varchar
d_type2 varchar
);

create function tst_func() returns setof returntype as
'
declare
r returntype%rowtype;
rLoopA RECORD;
rLoopB RECORD;
begin

for rLoopA IN
select a, b from foo where argle
loop

r.a := rLoopA.a;
r.b := rLoopA.b;

for rLoopB IN
select distinct on (foo, bar) foo, bar, data from sometable where bargle
-- this select may return a row for for every column of returntype
-- if a row/column is not returned, it should show null in the result set
loop

if foo = type1 and bar = c then
r.c_type1 := rLoopB;
else
r.c_type1 := NULL; -- note the explicit set to null
end if;

if foo = type2 and bar = c then
r.c_type2 := rLoopB;
else
r.c_type2 := NULL;
end if;

if foo = type1 and bar = d then
r.d_type1 := rLoopB;
else
r.d_type1 := NULL;
end if;

if foo = type2 and bar = d then
r.d_type2 := rLoopB;
else
r.d_type2 := NULL;
end if;

end loop;

return next r;

end loop;


end;
' language 'plpgsql';

sszabo, 2003/05/15 19:18 EST (通过网页): 我认为最好能有一种方法显式地设置行类型（也许是行值构造函数），因为也有一些情况是显式地将字段设置为 NULL 是你所不希望的。

2003/05/26 08:05 EST (通过网页): 当我想尝试运行最后一个示例时，创建类型 numtype 为 (num int, doublenum int)；创建或替换函数 GetNum(int) 返回 setof numtype 为 ' declare r numtype%rowtype; i int; begin for i in 1 .. $1 loop r.num := i; r.doublenum := i*2; return next r; end loop; return; end ' 语言 'plpgsql'; 它不起作用.... 它给了我这个错误： WARNING: 在执行 PL/pgSQL 函数 getnum 时发生错误 WARNING: 第 8 行在 return next ERROR: 在无法接受集合的上下文中调用了集合值函数 有没有人能告诉我它出了什么问题？？？ 2003/05/27 11:31 EST (通过网页): 你是像 select GetNum(1); 还是 select * from GetNum(1); ?

2003/05/28 11:34 EST (通过网页): 是的，我同意.. 这个教程必须成为 Postgresql 函数文档的一部分。我在阅读文档时对返回集合的函数感到困惑，但在浏览 www.postgresql.org 后，搜索并找到了这个教程，我很高兴... 谢谢

2003/05/29 08:00 EST (通过网页): 我是把它调用为 select * from GetNum(1);

2003/06/04 08:12 EST (通过网页): 在这里添加你的评论... 2003/06/26 04:31 EST (通过网页): 调用函数 GetRows(text) 错误： testdb=# select * from GetRows('department') as dept(deptid integer, deptname text); ERROR: 解析器：在或附近解析错误 "(" testdb=# 为什么？

2003/06/26 12:13 EST (通过网页): 我同意这份文档应该包含在 PostGre 文档中。你是否知道一种更好的方法来创建函数结果类型？事实上，当我们已经知道函数返回的类型时，显式地声明类型是一种损失。一种更好的方法是，根据你的示例：创建类型 holder 为 (departmentid employe.departmentid%type, totalsalary int8); 你知道是否有方法可以做到这一点吗？

2003/06/30 08:25 EST (通过网页): plpgsql 似乎有一些限制，阻止你在查询的 SELECT 列表中使用集合值函数。你可以执行“select foo, set_of_things(bar) from mytable”，如果 set_of_things() 是一个 SQL 函数，或者是一个 C 函数（显然）- 这从尝试弄清楚 contrib/intagg 中的 int_array_enum() 函数是如何做到的而开始的- 但如果不是一个 PL/pgSQL 函数。然而，这确实给你提供了一种变通方法：你可以从一个 SQL 函数中调用 PL/pgSQL 函数。一个简单的例子：创建函数 pfoo(int) 返回 setof int 语言 'plpgsql' 为 'declare b alias for $1; x int; begin for x in 1..b loop return next x; end loop; return; end;'；创建函数 foo(int) 返回 setof int 语言 'sql' 为 'select * from pfoo($1)'；select 1, pfoo(5); /* 会给你一个错误 */ select 1, foo(5); /* 工作正常 */ (抱歉，这个文本框太宽，太短了...)

2003/10/14 18:11 EST (通过网页): 如果我创建一个 sql 字符串，并且列的数量是动态确定的，那么我如何执行创建的字符串？事实上，setof 意味着我知道记录的类型，但这个信息只有在运行时才知道。

2003/10/15 03:23 EST (通过网页): 嗨，因为我是 postgreSQL 和函数的新手，我尝试执行上面给出的第一个示例 GetEmployees()。我已经创建了表格和记录，如上所示，但我无法让函数运行。如果我给出 SELECT GetEmployees(); 我得到一系列明显的数字。这可能没问题。但是，如果我给出 SELECT * from GetEmployees(); 那么我得到 ---> ERROR: 解析器：在或附近解析错误 "(". 有没有人知道这个示例有什么问题？我使用的是 postgreSQL 版本 7.2.2 谢谢。

2003/10/17 19:26 EST (通过网页): 新手：这篇文章需要 PostgreSQL 版本 7.3 或更高版本。你无法在 7.2 中执行它。-Josh

2003/10/24 05:22 EST (通过网页): 修复了此页面的恼人格式。有人将他们的整个评论都包裹在 pre /pre 中，使页面布局令人困惑地变宽。

2003/10/24 16:45 EST (通过网页): 只是一个我遇到的问题的简短说明。我的一个表有一个递归关系。旧记录-> 新记录。编写一个返回任何给定入口点的最新行的函数有点棘手，因为我看到的任何内容都没有提到递归。以下是我所做的。

create or replace function get_current_rec(numeric) returns setof rec as
'
declare
r rec%rowtype;
begin
for r in select a, b, c, d as total from table where key = $1 loop
if r.replaced_by IS NULL THEN
return next r;
ELSE
raise notice ''trying to fetch record for %'',r.replaced_by;
select into r * from get_current_rec(r.replaced_by);
return next r;
end if;
end loop;
return;
end
'
language 'plpgsql';

很长一段时间我被困在返回 0 条记录上。事实证明，选择进入 r 并调用 next 修复了这个问题。我已经用 4 级递归测试过它，并且它有效，所以我相信它是正确的。

2003/10/24 17:31 EST (通过网页): 是否有一种方法可以让函数返回自定义类型的聚合？类似于

create type foo as (blah int, blum int);
create type bar as (words text, when timestamp);
create type things as (foo, bar);

create function something() returns setof things as '
statements;
'

2003/10/25 15:33 EST (通过网页): 有没有人有返回 SETOF RECORD 的 C 函数的示例？[tablefunc.c 这样做，但对于 ROWTYPE，而不是 RECORD] 调用此函数的语法是什么？[也许：SELECT * FROM c_fcn() AS (a int, b text);]

2003/11/03 00:12 EST (通过网页): 谢谢，这帮助很大。需要注意的是：如果你在 EXECUTE SELECT 中处理 WHERE 子句，你可能需要对你的字符串变量（如果它们被传递进来）使用 quote_literal()。例如：CREATE FUNCTION public.sp_get_baz_for_cust(bpchar) RETURNS SETOF bpchar AS ' DECLARE cust_id ALIAS FOR $1; baz_num CHAR( 15 ); selected_baz RECORD; BEGIN FOR selected_baz IN EXECUTE SELECT baz_number FROM baz_table WHERE customer_id = || quote_literal( cust_id ) LOOP RETURN NEXT selected_baz.ticket_number; END LOOP; RETURN; END; 如果你不使用 quote_literal()，查询往往会因特殊字符（如冒号、破折号等）而阻塞。我认为它也不喜欢空格。我尝试将字符串构建为 SELECT baz_number FROM baz_table WHERE customer_id = ' || cust_id || ' - 不行。quote_literal() 是解决方案。

2003/11/03 00:16 EST (通过网页): 对上一篇文章的冗长内容表示歉意。这是我在这里的第一篇文章，没有意识到我需要格式化。以下是以（希望）更友好的格式呈现的内容

谢谢，这帮助很大。

需要注意的是：如果你在 EXECUTE SELECT 中处理 WHERE 子句，你可能需要对你的字符串变量（如果它们被传递进来）使用 quote_literal()。例如

CREATE FUNCTION public.sp_get_baz_for_cust(bpchar) RETURNS SETOF bpchar AS '
DECLARE cust_id ALIAS FOR $1;
baz_num CHAR( 15 );
selected_baz RECORD;

BEGIN

FOR selected_baz IN EXECUTE ''SELECT baz_number FROM baz_table WHERE customer_id = '' || quote_literal( cust_id ) LOOP
RETURN NEXT selected_baz.ticket_number;
END LOOP;

RETURN;

END;

如果你不使用 quote_literal()，查询往往会因特殊字符（如冒号、破折号等）而阻塞。我认为它也不喜欢空格。我尝试将字符串构建为 SELECT baz_number FROM baz_table WHERE customer_id = ' || cust_id || ' - 不行。quote_literal() 是解决方案。

2004/04/05 13:55 AST (通过网页): 是否有办法获取记录中的第 n 个项目？类似于 DECLARE rec RECORD; BEGIN rec.$1 := 1; (...)

2004/05/22 09:02 AST (通过网页): 如果你来到这里想知道如何从函数中返回多个值（如在 Oracle PL/SQL 中）：CREATE FUNCTION temp() RETURNS record DECLARE v_record RECORD; BEGIN select 1, 6, 8 into v_record; return v_record; END; 然后你执行：select * from temp() as (int4, int4, int4)

2005/03/13 14:59 GMT (通过网页): 在这里添加你的评论...

2005/07/11 16:59 GMT (通过网页): 我是 PostgreSQL 的新手！我有一个名为“events”的表，另一个名为“event_parameter”，以及其他一些与这两个表相关的表。我想要的是创建一个函数，当事件发生时，它将管理这些表。有人可以帮助我吗？！谢谢

2005/08/02 10:54 GMT (通过网页): 你可以考虑使用触发器

从“https://wiki.postgresql.ac.cn/index.php?title=Return_more_than_one_row_of_data_from_PL/pgSQL_functions&oldid=17343”中检索