PostgreSQL function返回多行的操作
1.建表
postgres=#createtabletb1(idinteger,namecharactervarying); CREATETABLE postgres=# postgres=#insertintotb1selectgenerate_series(1,5),'aa'; INSERT05
2.返回单字段的多行（returnssetofdatatype）
不指定out参数，使用returnnextxx：
createorreplacefunctionfunc01()returnssetofcharactervaryingas$$ declare ncharactervarying; begin foriin1..5loop selectnameintonfromtb1whereid=i; returnnextn; endloop; end $$languageplpgsql;
指定out参数，使用returnnext：
createorreplacefunctionfunc02(outcharactervarying)returnssetofcharactervaryingas$$ begin foriin1..5loop selectnameinto$1fromtb1whereid=i; returnnext; endloop; end $$languageplpgsql;
使用returnquery：
createorreplacefunctionfunc03()returnssetofcharactervaryingas$$ begin foriin1..5loop returnquery(selectnamefromtb1whereid=i); endloop; end $$languageplpgsql;
3.返回多列的多行（returnssetogrecord）
不指定out参数，使用returnnextxx：
createorreplacefunctionfunc04()RETURNSSETOFRECORDas$$ declare rrecord; begin foriin1..5loop select*intorfromtb1whereid=i; returnnextr; endloop; end; $$languageplpgsql;
在使用func04的时候注意，碰到问题列下：
问题一：
postgres=#selectfunc04(); ERROR:set-valuedfunctioncalledincontextthatcannotacceptaset CONTEXT:PL/pgSQLfunctionfunc04()line7atRETURNNEXT
解决：
Ifyoucallyourset-returningfunctionthewrongway(IOWthewayyoumightnormallycallafunction),youwillgetthiserrormessage:Set-valuedfunctioncalledincontextthatcannotacceptaset.Incorrect:selectsr_func(arg1,arg2,…);Correct:select*fromsr_func(arg1,arg2,…);
问题二：
postgres=#select*fromfunc04(); ERROR:acolumndefinitionlistisrequiredforfunctionsreturning"record" LINE1:select*fromfunc04();
解决：
postgres=#select*fromfunc04()ast(idinteger,namecharactervarying); id|name ----+------ 1|aa 2|aa 3|aa 4|aa 5|aa (5rows)
这个问题在func04如果指定out参数就不会有问题，如下func05所示：
指定out参数，使用returnnext：
createorreplacefunctionfunc05(outout_idinteger,outout_namecharactervarying)returnssetofrecordas$$ declare rrecord; begin foriin1..5loop select*intorfromtb1whereid=i; out_id:=r.id; out_name:=r.name; returnnext; endloop; end; $$languageplpgsql;
postgres=#select*fromfunc05(); id|name ----+------ 1|aa 2|aa 3|aa 4|aa 5|aa (5rows)
使用returnquery：
createorreplacefunctionfunc06()returnssetofrecordas$$ begin foriin1..5loop returnquery(selectid,namefromtb1whereid=i); endloop; end; $$languageplpgsql;
postgres=#select*fromfunc06()ast(idinteger,namecharactervarying); id|name ----+------ 1|aa 2|aa 3|aa 4|aa 5|aa (5rows)
补充：Postgresql-plpgsql-从Function中查询并返回多行结果
通过plpgsql查询表，并返回多行的结果。
关于创建实验表插入数据这里就不说啦
返回查询结果
mytest=#createorreplacefunctiontest_0830_5()returnssetoftest mytest-#as$$ mytest$#DECLARE mytest$#rtest%rowtype;--将 mytest$#BEGIN mytest$#FORrIN mytest$#SELECT*FROMtestWHEREid>0 mytest$#LOOP mytest$#RETURNNEXTr; mytest$#ENDLOOP; mytest$#RETURN; mytest$#END mytest$#$$languageplpgsql; CREATEFUNCTION mytest=#selecttest_0830_5(1); test_0830_5 ------------------------------------------ (2,abcabc,"2018-08-3009:26:14.392187") ...... (11,abcabc,"2018-08-3009:26:14.392187") (10rows) mytest=#select*fromtest_0830_5(); id|col1|col2 ----+--------+---------------------------- 2|abcabc|2018-08-3009:26:14.392187 ...... 11|abcabc|2018-08-3009:26:14.392187 (10rows)
返回某列
mytest=#CREATEORREPLACEFUNCTIONtest_0830_6(date)RETURNSSETOFintegerAS$$
mytest$#BEGIN
mytest$#RETURNQUERYSELECTid
mytest$#FROMtest
mytest$#WHEREcol2>=$1
mytest$#ANDcol2<($1+1);
mytest$#IFNOTFOUNDTHEN
mytest$#RAISEEXCEPTION'Noidat%.',$1;
mytest$#ENDIF;
mytest$#RETURN;
mytest$#END
mytest$#$$
mytest-#LANGUAGEplpgsql;
CREATEFUNCTION
mytest=#selecttest_0830_6('2018-08-30');
test_0830_6
-------------
2
......
11
(10rows)
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。