如何从PL/pgSQL 函数中返回多行数据

PgSQL 自7.3起支持SRF( Set Returning Func. 集合返回函数) 配合有一些新的函数权限选项，
使 schema 的设置更灵活性。SRF 除了手册里提到的内置函数 generate_series generate_subscript 外，自定义函数也可返回集合。 下面示例摘译自 PgSQL wiki ：PL/PgSQL如何返回多行结果

我们从处理简单表单函数说起。

createtabledepartment(idintprimarykey,nametext);
createtableemployee(idintprimarykey,nametext,salaryint,departmentidintreferencesdepartment);
insertintodepartmentvalues(1,'Management');
insertintodepartmentvalues(2,'IT');
insertintoemployeevalues(1,'JohnSmith',30000,1);
insertintoemployeevalues(2,'JaneDoe',50000,1);
insertintoemployeevalues(3,'JackJackson',60000,2);

SRF 可以返回的数据类型可以是现有表中定义过的 rowtype 或通用的 record 类型。
首先我们看一个简单的SQL函数返回现有表中的 rowtype

createfunctionGetEmployees()
returnssetofemployeeas'select*fromemployee;'
language'sql';

这个非常简单的函数直接返回 employee 中的所有行：
其返回类型为 setof employee 即返回由 employee 各行组成的行集合，
其主体采用简单SQL语句，生成输出的行。

SRF 可以在查询中代替 FROM 中的 表 或 子查询 。
例如用函数返回所有id>2的聘员只要：

select*fromGetEmployees()whereid>2;

很好，但要返回更复杂的数据怎么办？
例如： 部门列表及其中所有聘员的薪水，
要返回现有记录类型，你需要创建一个虚构类型来保存输出的数据，
例如：

createtypeholderas(departmentidint,totalsalaryint8);

这里创建了新的复合类型 holder 由名为 departmentid 的 int
和名为 totalsalary 的 bigint 组成，我们可以让函数返回此类型的集合：
这次我们用 SQL 和 PL/pgSQL 来分别实现这个函数:

createfunctionSqlDepartmentSalaries()
returnssetofholderas
'
select
departmentid,sum(salary)astotalsalary
fromGetEmployees()
groupbydepartmentid
'
language'sql';

createorreplacefunctionPLpgSQLDepartmentSalaries()
returnssetofholderas
'
declare
rholder%rowtype;
begin
forrinselectdepartmentid,sum(salary)astotalsalary
fromGetEmployees()groupbydepartmentidloop
returnnextr;
endloop;
return;
end
'
language'plpgsql';

SQL的版本与之前的很相像，返回由 holder (int,int8) 类型定义的 rowtype，

返回的行由函数体中的 group by 查询决定。

PL/pgSQL 版本稍复杂，首先变量 r 被声明为 rowtype holder 。用此变量保存行函数体中的查询结果，函数主体对 group by 查询的结果循环执行，r 依次被赋值为结果中的各行，循环体中采用了新的return形式 'return next' 即将结果追加到返回的集合中，但不会造成函数返回。目前 PL/pgSQL 的 SRF 函数在全部结果生成完毕前不会返回。如果集合很大会写入硬盘。此限制未来的版本中也许会改变。

新函数的使用与之前相同，

select*fromPLpgSQLDepartmentSalaries();

PL/pgSQL 函数还可以对结果进行运算，只返回某些结果。
例如：要计算部门运营成本：部门总薪资 70,000 以上的开销是 75%,其他为 50%，

返回薪资+开销>100,000的部门的部门id。

createorreplacefunctionExpensiveDepartments()
returnssetofintas
'
declare
rholder%rowtype;
begin
forrinselectdepartmentid,sum(salary)astotalsalary
fromGetEmployees()groupbydepartmentidloop
if(r.totalsalary>70000)then
r.totalsalary:=CAST(r.totalsalary*1.75asint8);
else
r.totalsalary:=CAST(r.totalsalary*1.5asint8);
endif;
if(r.totalsalary>100000)then
returnnextr.departmentid;
endif;
endloop;
return;
end
'
language'plpgsql';

比较一下本次与之前 PLpgSQLDepartmentSales() 的区别。

因为本次只需要返回高成本部门的 department id
函数返回一个整数集合 (department id) 而非之前的复合类型。

if(r.totalsalary>70000)then
r.totalsalary:=CAST(r.totalsalary*1.75asint8);
else
r.totalsalary:=CAST(r.totalsalary*1.5asint8);
endif;

然后判断 totalsalary 是否大于 100,000 如果为真，则返回识别符

if(r.totalsalary>100000)then
returnnextr.departmentid;
endif;

注意本次 return next 没有返回记录 r 而只有 departmentid,
如果需要同时返回 薪资总额与开销之和，

前面声明中可以定义为 return setof holder 这里使用 return next r;

以上函数返回的复合类型使用的前提是返回的类型与函数的 return 声明中的相同。
如果不同，SQL版本在创建时会报错，PL/pgSQL 版本在运行中会出现错。
但如果结果中的类型只能在运行中确定该怎么办？
此时你可以声明 return setof record 以返回复合类型的集合，

返回的类型可以调用时设置。例如我们要创建一个函数返回指定表中的所有行：

createorreplacefunctionGetRows(text)
returnssetofrecordas
'
declare
rrecord;
begin
forrinEXECUTE''select*from''||$1loop
returnnextr;
endloop;
return;
end
'
language'plpgsql';

调用此函数时比之前的要稍复杂，查询中需要指定函数返回的数据。
PostgreSQL 对 SRF函数 的处理与子查询相似，语法上与为子查询中别名的设定相似。

select*fromGetRows('Department')asdept(deptidint,deptnametext);

我们将 Department 作为参数传入，结果应该与 Department 表的一般记录相同，

由一个 INT 和一个 TEXT 组成。于是我们告诉PgSQL，结果dept 为别名，

包含一个名为 deptid 的整数和 deptname 的文本。

最后我们试试完全用 PL/pgSQL函数生成数据。让我们从最简单的做起：

写一个函数，接收返回1到任意数间的所有数，以及这个他们的二倍。

我们先写一个以预定义类型的为内部和返回类型的版本。

createtypenumtypeas(numint,doublenumint);
createorreplacefunctionGetNum(int)
returnssetofnumtypeas
'
declare
rnumtype%rowtype;
iint;
begin
foriin1..$1loop
r.num:=i;
r.doublenum:=i*2;
returnnextr;
endloop;
return;
end
'
language'plpgsql';

函数非常简单，声明中 r 为名为 numtype 的自定义 rowtype 。

将1到参数间的每个值，赋给 num 和 doublenum

然后 return next r 将结果加入输出集合的队列中；

用 record 类型可以实现通用效果，免去函数外的类型声明，

不过做起来会更复杂而且需要多一次 select 调用。

类似返回多个结果的还有 动态 SQL 查询语句

（PREPARE STATEMENT... + EXECUTE...INTO...USING + DEALLOCATE PREPARE）

通过返回 指针 也可以实现返回多行结果。

https://www.postgresql.org/docs/current/static/ecpg-dynamic.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!