神剑山庄资源网 Design By www.hcban.com
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;
在使用func04的时候注意,碰到问题列下:
问题一:
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)
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。
神剑山庄资源网 Design By www.hcban.com
神剑山庄资源网
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件!
如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
神剑山庄资源网 Design By www.hcban.com
暂无PostgreSQL function返回多行的操作的评论...
更新日志
2024年10月18日
2024年10月18日
- 群星《歌手2024 第4期》[FLAC/分轨][563.76MB]
- RiffKitten-ChaosParade(2024)[24-44,1]FLAC
- RuneKlakegg-Nattevandrer(2024)[24-96]\FLAC
- 赖冰霞.2006-金嗓典藏辑2IN1(南方金点系列)2CD【南方】【WAV+CUE】
- 群星.1988-国语金曲尽精英VOL.2【瑞成】【WAV+CUE】
- 张宇.1999-雨一直下【EMI百代】【WAV+CUE】
- 袁娅维.2021-月亮失眠了(DELUXE)【华纳】【FLAC分轨】
- AlanBroadbent-JazzFunk(2024)[24-44,1]
- 梅艳芳《绝代芳华》开盘母带[低速原抓WAV+CUE]
- ABC唱片-《外国精逊母带直刻神奇黑胶[APE+CUE].
- 群星《天赐的声音第五季 第6期》[320K/MP3][101.43MB]
- 群星《天赐的声音第五季 第6期》[FLAC/分轨][526.16MB]
- 群星《说唱梦工厂 第2期》[320K/MP3][96.37MB]
- 杨宗宪.1994-留你留袂着【有容唱片】【WAV+CUE】
- 李翊君.2005-勇敢的爱【创意唱片】【WAV+CUE】