十年网站开发经验+多家企业客户+靠谱的建站团队
量身定制+运营维护+专业推广+无忧售后，网站问题一站解决
1.建表
不指定out参数，使用returnnextxx：
createorreplacefunctionfunc01()returnssetofcharactervaryingas$$declarencharactervarying;beginforiin1..5loopselectnameintonfromtb1whereid=i;returnnextn;endloop;end$$languageplpgsql;指定out参数，使用returnnext：
createorreplacefunctionfunc02(outcharactervarying)returnssetofcharactervaryingas$$beginforiin1..5loopselectnameinto$1fromtb1whereid=i;returnnext;endloop;end$$languageplpgsql;使用returnquery：
createorreplacefunctionfunc03()returnssetofcharactervaryingas$$beginforiin1..5loopreturnquery(selectnamefromtb1whereid=i);endloop;end$$languageplpgsql;3.返回多列的多行（returnssetogrecord）
createorreplacefunctionfunc04()RETURNSSETOFRECORDas$$declarerrecord;beginforiin1..5loopselect*intorfromtb1whereid=i;returnnextr;endloop;end;$$languageplpgsql;在使用func04的时候注意，碰到问题列下：问题一：
postgres=#selectfunc04();ERROR:set-valuedfunctioncalledincontextthatcannotacceptasetCONTEXT: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|aa2|aa3|aa4|aa5|aa(5rows)这个问题在func04如果指定out参数就不会有问题，如下func05所示：
指定out参数，使用returnnext：
createorreplacefunctionfunc05(outout_idinteger,outout_namecharactervarying)returnssetofrecordas$$declarerrecord;beginforiin1..5loopselect*intorfromtb1whereid=i;out_id:=r.id;out_name:=r.name;returnnext;endloop;end;$$languageplpgsql;postgres=#select*fromfunc05();id|name----+------1|aa2|aa3|aa4|aa5|aa(5rows)使用returnquery：
createorreplacefunctionfunc06()returnssetofrecordas$$beginforiin1..5loopreturnquery(selectid,namefromtb1whereid=i);endloop;end;$$languageplpgsql;postgres=#select*fromfunc06()ast(idinteger,namecharactervarying);id|name----+------1|aa2|aa3|aa4|aa5|aa(5rows)补充：Postgresql-plpgsql-从Function中查询并返回多行结果
通过plpgsql查询表，并返回多行的结果。
关于创建实验表插入数据这里就不说啦
返回查询结果
mytest=#createorreplacefunctiontest_0830_5()returnssetoftestmytest-#as$$mytest$#DECLAREmytest$#rtest%rowtype;--将mytest$#BEGINmytest$#FORrINmytest$#SELECT*FROMtestWHEREid>0mytest$#LOOPmytest$#RETURNNEXTr;mytest$#ENDLOOP;mytest$#RETURN;mytest$#ENDmytest$#$$languageplpgsql;CREATEFUNCTIONmytest=#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)