PostgreSQL function返回多行的操作
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)
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。
