• 售前

  • 售后

热门帖子
入门百科

PostgreSQL function返回多行的操纵

[复制链接]
绘粹凭 显示全部楼层 发表于 2021-10-26 12:18:40 |阅读模式 打印 上一主题 下一主题
1. 建表
  1. postgres=# create table tb1(id integer,name character varying);
  2. CREATE TABLE
  3. postgres=#
  4. postgres=# insert into tb1 select generate_series(1,5),'aa';
  5. INSERT 0 5
复制代码
2. 返回单字段的多行(returns setof datatype)
不指定out参数,利用return next xx:
  1. create or replace function func01()returns setof character varying as $$
  2. declare
  3. n character varying;
  4. begin
  5. for i in 1..5 loop
  6. select name into n from tb1 where id=i;
  7. return next n;
  8. end loop;
  9. end
  10. $$ language plpgsql;
复制代码
指定out参数,利用return next:
  1. create or replace function func02(out character varying)returns setof character varying as $$
  2. begin
  3. for i in 1..5 loop
  4. select name into $1from tb1 where id=i;
  5. return next;
  6. end loop;
  7. end
  8. $$ language plpgsql;
复制代码
利用return query:
  1. create or replace function func03()returns setof character varying as $$
  2. begin
  3. for i in 1..5 loop
  4. return query(select name from tb1 where id=i);
  5. end loop;
  6. end
  7. $$language plpgsql;
复制代码
3. 返回多列的多行(returns setog record)
不指定out参数,利用return next xx:
  1. create or replace function func04()RETURNS SETOF RECORD as $$
  2. declare
  3. r record;
  4. begin
  5. for i in 1..5 loop
  6. select * into r from tb1 where id=i;
  7. return next r;
  8. end loop;
  9. end;
  10. $$language plpgsql;
复制代码
在利用func04的时间留意,遇到问题列下:

问题一:
  1. postgres=# select func04();
  2. ERROR: set-valued function called in context that cannot accept a set
  3. CONTEXT: PL/pgSQL function func04() line 7 at RETURN NEXT
复制代码
解决:
  1. 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, …);
复制代码
问题二:
  1. postgres=# select * from func04();
  2. ERROR: a column definition list is required for functions returning "record"
  3. LINE 1: select * from func04();
复制代码
解决:
  1. postgres=# select * from func04() as t(id integer,name character varying);
  2. id | name
  3. ----+------
  4. 1 | aa
  5. 2 | aa
  6. 3 | aa
  7. 4 | aa
  8. 5 | aa
  9. (5 rows)
复制代码
这个问题在func04如果指定out参数就不会有问题,如下func05所示:
指定out参数,利用return next:
  1. create or replace function func05(out out_id integer,out out_name character varying)returns setof record as $$
  2. declare
  3. r record;
  4. begin
  5. for i in 1..5 loop
  6. select * into r from tb1 where id=i;
  7. out_id:=r.id;
  8. out_name:=r.name;
  9. return next;
  10. end loop;
  11. end;
  12. $$language plpgsql;
复制代码
  1. postgres=# select * from func05();
  2. id | name
  3. ----+------
  4. 1 | aa
  5. 2 | aa
  6. 3 | aa
  7. 4 | aa
  8. 5 | aa
  9. (5 rows)
复制代码
利用return query:
  1. create or replace function func06()returns setof record as $$
  2. begin
  3. for i in 1..5 loop
  4. return query(select id,name from tb1 where id=i);
  5. end loop;
  6. end;
  7. $$language plpgsql;
复制代码
  1. postgres=# select * from func06() as t(id integer,name character varying);
  2. id | name
  3. ----+------
  4. 1 | aa
  5. 2 | aa
  6. 3 | aa
  7. 4 | aa
  8. 5 | aa
  9. (5 rows)
复制代码
增补:Postgresql - plpgsql - 从Function中查询并返回多行结果
通过plpgsql查询表,并返回多行的结果。
关于创建实行表插入数据这里就不说啦
返回查询结果
  1. mytest=# create or replace function test_0830_5() returns setof test
  2. mytest-# as $$
  3. mytest$# DECLARE
  4. mytest$# r test%rowtype; -- 将
  5. mytest$# BEGIN
  6. mytest$# FOR r IN
  7. mytest$# SELECT * FROM test WHERE id > 0
  8. mytest$# LOOP
  9. mytest$# RETURN NEXT r;
  10. mytest$# END LOOP;
  11. mytest$# RETURN;
  12. mytest$# END
  13. mytest$# $$ language plpgsql;
  14. CREATE FUNCTION
  15. mytest=# select test_0830_5(1);
  16. test_0830_5
  17. ------------------------------------------
  18. (2,abcabc,"2018-08-30 09:26:14.392187")
  19. ......
  20. (11,abcabc,"2018-08-30 09:26:14.392187")
  21. (10 rows)
  22. mytest=# select * from test_0830_5();
  23. id | col1 | col2
  24. ----+--------+----------------------------
  25. 2 | abcabc | 2018-08-30 09:26:14.392187
  26. ......
  27. 11 | abcabc | 2018-08-30 09:26:14.392187
  28. (10 rows)
复制代码
返回某列
  1. mytest=# CREATE OR REPLACE FUNCTION test_0830_6(date) RETURNS SETOF integer AS $$
  2. mytest$# BEGIN
  3. mytest$# RETURN QUERY SELECT id
  4. mytest$# FROM test
  5. mytest$# WHERE col2 >= $1
  6. mytest$# AND col2 < ($1 + 1);
  7. mytest$# IF NOT FOUND THEN
  8. mytest$# RAISE EXCEPTION 'No id at %.', $1;
  9. mytest$# END IF;
  10. mytest$# RETURN;
  11. mytest$# END
  12. mytest$# $$
  13. mytest-# LANGUAGE plpgsql;
  14. CREATE FUNCTION
  15. mytest=# select test_0830_6('2018-08-30');
  16. test_0830_6
  17. -------------
  18. 2
  19. ......
  20. 11
  21. (10 rows)
复制代码
以上为个人经验,希望能给大家一个参考,也希望大家多多支持草根技能分享。如有错误或未思量完全的地方,望不吝见教。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作