PostgreSQL function返回多行的操作

1. 建表

postgres=# create table tb1(id integer,name character VARying);
    CREATE TABLEpostgres=# postgres=# insert into tb1 select generate_series(1,5),'aa';
    INSERT 0 5 

2. 返回单字段的多行（returns setof datatyPE）

不指定out参数，使用return next xx：

create or replace function func01()returns setof character varying as $$declaren character varying;
    begin for i in 1..5 loop select name into n From tb1 where id=i;
     return next n;
     end loop;
    end$$ language plpgSQL;
    

指定out参数，使用return next：

create or replace function func02(out character varying)returns setof character varying as $$begin for i in 1..5 loop select name into $1from tb1 where id=i;
     return next;
     end loop;
    end$$ language plpgsql;
    

使用return query：

create or replace function func03()returns setof character varying as $$begin for i in 1..5 loop return query(select name from tb1 where id=i);
     end loop;
    end$$language plpgsql;
    

3. 返回多列的多行（returns setog record）

不指定out参数，使用return next xx：

create or replace function func04()RETURNS SETOF RECORD as $$declare r record;
    begin for i in 1..5 loop select * into r from tb1 where id=i;
     return next r;
     end loop;
    end;
    $$language plpgsql;
    

在使用func04的时候注意，碰到问题列下：

问题一：

postgres=# select func04();
    ERROR: set-valued function called in context that cannot accept a setCONTEXT: PL/pgSQL function func04() line 7 at RETURN NEXT

解决：

If you call your set-returning function the wrong way (IOW the way you might normally call a function), you will get this error message: Set-valued function called in context that cannot accept a set. Incorrect: select sr_func(arg1, arg2, …);
     Correct: select * from sr_func(arg1, arg2, …);
    

问题二：

postgres=# select * from func04();
    ERROR: a column definITion list is required for functions returning "record"LINE 1: select * from func04();
    

解决：

postgres=# select * from func04() as t(id integer,name character varying);
     id | name ----+------ 1 | aa 2 | aa 3 | aa 4 | aa 5 | aa(5 rows)

这个问题在func04如果指定out参数就不会有问题，如下func05所示：

指定out参数，使用return next：

create or replace function func05(out out_id integer,out out_name character varying)returns setof record as $$declare r record;
    begin for i in 1..5 loop select * into r from tb1 where id=i;
     out_id:=r.id;
     out_name:=r.name;
     return next;
     end loop;
    end;
    $$language plpgsql;
    

postgres=# select * from func05();
     id | name ----+------ 1 | aa 2 | aa 3 | aa 4 | aa 5 | aa(5 rows)

使用return query：

create or replace function func06()returns setof record as $$begin for i in 1..5 loop return query(select id,name from tb1 where id=i);
     end loop;
    end;
    $$language plpgsql;
    

postgres=# select * from func06() as t(id integer,name character varying);
     id | name ----+------ 1 | aa 2 | aa 3 | aa 4 | aa 5 | aa(5 rows)

补充：Postgresql - plpgsql - 从Function中查询并返回多行结果

通过plpgsql查询表，并返回多行的结果。

关于创建实验表插入数据这里就不说啦

返回查询结果

mytest=# create or replace function test_0830_5() returns setof testmytest-# as $$mytest$# DECLAREmytest$# r test%rowtype;
     -- 将mytest$# BEGINmytest$# FOR r INmytest$# SELECT * FROM test WHERE id >
     0mytest$# LOOPmytest$# RETURN NEXT r;
    mytest$# END LOOP;
    mytest$# RETURN;
    mytest$# ENDmytest$# $$ language plpgsql;
    CREATE FUNCTION mytest=# select test_0830_5(1);
    test_0830_5------------------------------------------(2,abcabc,"2018-08-30 09:26:14.392187")......(11,abcabc,"2018-08-30 09:26:14.392187")(10 rows) mytest=# select * from test_0830_5();
    id | col1 | col2----+--------+----------------------------2 | abcabc | 2018-08-30 09:26:14.392187......11 | abcabc | 2018-08-30 09:26:14.392187(10 rows)

返回某列

mytest=# CREATE OR REPLACE FUNCTION test_0830_6(date) RETURNS SETOF integer AS $$mytest$# BEGINmytest$# RETURN QUERY SELECT idmytest$# FROM testmytest$# WHERE col2 >
    = $1mytest$# AND col2  ($1 + 1);
    mytest$# IF NOT FOUND THENmytest$# RaiSE EXCEPTION 'No id at %.', $1;
    mytest$# END IF;
    mytest$# RETURN;
    mytest$# ENDmytest$# $$mytest-# LANGUAGE plpgsql;
    CREATE FUNCTIONmytest=# select test_0830_6('2018-08-30');
    test_0830_6-------------2......11(10 rows)

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

• Postgresql创建新增、删除与修改触发器的方法
• PostgreSQL+Pgpool实现HA主备切换的操作
• PostgreSQL时间线(timeline)和History File的用法
• 基于postgresql行级锁for update测试
• 查看postgresql数据库用户系统权限、对象权限的方法
• Postgresql锁机制详解(表锁和行锁)
• postgres主备切换之文件触发方式详解

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！