GaussDB(DWS)自定义函数返回多结果集

在使用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)

from: https://bbs.huaweicloud.com/blogs/205964