在使用GaussDB(DWS)过程中经常会创建自定义函数，总结了多结果集返回的使用方法。
1.建表
postgres=> create table test_tb_01(id integer,name varchar);
NOTICE: The ‘DISTRIBUTE BY‘ clause is not specified. Using ‘id‘ as the distribution column by default.
HINT: Please use ‘DISTRIBUTE BY‘ clause to specify suitable data distribution column.
CREATE TABLE
postgres=> insert into test_tb_01 values(generate_series(1,5),‘aa‘);
INSERT 0 5
2.返回单列多行
- 使用return next variable：
create or replace function test_func_01()
return setof varchar
as
out_name varchar;
begin
for out_name in select name from test_tb_01 loop
return next out_name;
end loop;
end;
/
postgres=> select test_func_01();
test_func_01
--------------
aa
aa
aa
aa
aa
(5 rows)
- 指定out参数，使用return next：
create or replace function test_func_02(out out_name varchar)
return setof varchar
as
v_rec RECORD;
begin
for v_rec in select * from test_tb_01 loop
out_name := v_rec.name;
return next;
end loop;
end;
/
postgres=> select test_func_02();
test_func_02
--------------
aa
aa
aa
aa
aa
(5 rows)
- 使用return query：
create or replace function test_func_03()
return setof varchar
as
begin
return query(select name from test_tb_01);
end;
/
postgres=> select test_func_03();
test_func_03
--------------
aa
aa
aa
aa
aa
(5 rows)
3.返回多列的多行
- 使用return next variable：
create or replace function test_func_04()
RETURN SETOF RECORD as
declare
v_rec record;
begin
for v_rec in select * from test_tb_01 loop
return next v_rec;
end loop;
end;
/
自定义函数test_func_04的调用，需要注意如下问题：
postgres=> select test_func_04();
ERROR: Set-valued function called in context that cannot accept a set when init tuple store for RETURN NEXT/RETURN QUERY.
CONTEXT: PL/pgSQL function test_func_04() line 6 at RETURN NEXT
referenced column: test_func_04
postgres=> select * from test_func_04();
ERROR: a column definition list is required for functions returning "record"
LINE 1: select * from test_func_04();
需要使用as子句来处理结果集
postgres=> select * from test_func_04() as t(id integer,name varchar);
id | name
----+------
1 | aa
2 | aa
3 | aa
4 | aa
5 | aa
(5 rows)
指定out参数就不会有问题，如下test_func_05所示：
- 指定out参数，使用return next：
create or replace function test_func_05(out out_id integer,out out_name varchar)
return setof record as
declare
v_rec record;
begin
for v_rec in select * from test_tb_01 loop
out_id := v_rec.id;
out_name := v_rec.name;
return next;
end loop;
end;
/
postgres=> select * from test_func_05();
id | name
----+------
1 | aa
2 | aa
3 | aa
4 | aa
5 | aa
(5 rows)
- 使用return query：
create or replace function test_func_06()
return setof record as
begin
return query(select id,name from test_tb_01);
end;
/
postgres=> select * from test_func_06() as t(id integer,name varchar);
id | name
----+------
1 | aa
2 | aa
3 | aa
4 | aa
5 | aa
(5 rows)