脚本宝典收集整理的这篇文章主要介绍了PostgreSQL function返回多行的操作，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。
1. 建表
postgres=# create table tb1(id integer,name character VARying); CREATE TABLE postgres=# 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 $$ declare n 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;
问题一：
postgres=# select func04(); ERROR: set-valued function called in context that cannot accept a set CONTEXT: 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 test mytest-# as $$ mytest$# DECLARE mytest$# r test%rowtype; -- 将 mytest$# BEGIN mytest$# FOR r IN mytest$# SELECT * FROM test WHERE id > 0 mytest$# LOOP mytest$# RETURN NEXT r; mytest$# END LOOP; mytest$# RETURN; mytest$# END mytest$# $$ 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$# BEGIN mytest$# RETURN QUERY SELECT id mytest$# FROM test mytest$# WHERE col2 >= $1 mytest$# AND col2 < ($1 + 1); mytest$# IF NOT FOUND THEN mytest$# RaiSE EXCEPTION 'No id at %.', $1; mytest$# END IF; mytest$# RETURN; mytest$# END mytest$# $$ mytest-# LANGUAGE plpgsql; CREATE FUNCTION mytest=# select test_0830_6('2018-08-30'); test_0830_6 ------------- 2 ...... 11 (10 rows)
以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本宝典。如有错误或未考虑完全的地方，望不吝赐教。
以上是脚本宝典为你收集整理的PostgreSQL function返回多行的操作全部内容，希望文章能够帮你解决PostgreSQL function返回多行的操作所遇到的问题。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。