怎么在PostgreSQL中使用function返回多行的数据-创新互联
这期内容当中小编将会给大家带来有关怎么在PostgreSQL中使用function返回多行的数据,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。
10年的宿城网站建设经验,针对设计、前端、开发、售后、文案、推广等六对一服务,响应快,48小时及时工作处理。成都全网营销的优势是能够根据用户设备显示端的尺寸不同,自动调整宿城建站的显示方式,使网站能够适用不同显示终端,在浏览器中调整网站的宽度,无论在任何一种浏览器上浏览网站,都能展现优雅布局与设计,从而大程度地提升浏览体验。创新互联从事“宿城网站设计”,“宿城网站推广”以来,每个客户项目都认真落实执行。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)
上述就是小编为大家分享的怎么在PostgreSQL中使用function返回多行的数据了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注创新互联行业资讯频道。
名称栏目:怎么在PostgreSQL中使用function返回多行的数据-创新互联
本文地址:http://scyanting.com/article/dsjsee.html