1. 建表
|
1
2
3
4
5
|
postgres=# create table tb1(id integer,name character varying);CREATE TABLEpostgres=# postgres=# insert into tb1 select generate_series(1,5),'aa';INSERT 0 5 |
2. 返回单字段的多行（returns setof datatype）
不指定out参数，使用return next xx：
|
1
2
3
4
5
6
7
8
9
10
|
declaren 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：
|
1
2
3
4
5
6
7
8
|
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：
|
1
2
3
4
5
6
7
|
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：
|
1
2
3
4
5
6
7
8
9
10
|
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的时候注意，碰到问题列下：
问题一：
|
1
2
3
|
postgres=# select func04();ERROR: set-valued function called in context that cannot accept a setCONTEXT: 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
2
3
|
postgres=# select * from func04();ERROR: a column definition list is required for functions returning "record"LINE 1: select * from func04(); |
解决：
|
1
2
3
4
5
6
7
8
9
|
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：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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; |
|
1
2
3
4
5
6
7
8
9
|
postgres=# select * from func05(); id | name----+------ 1 | aa 2 | aa 3 | aa 4 | aa 5 | aa(5 rows) |
使用return query：
|
1
2
3
4
5
6
7
|
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; |
|
1
2
3
4
5
6
7
8
9
|
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查询表，并返回多行的结果。
关于创建实验表插入数据这里就不说啦
返回查询结果
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
mytest=# create or replace function test_0830_5() returns setof testmytest-# as $$mytest$# DECLAREmytest$# r test%rowtype; -- 将mytest$# BEGINmytest$# FOR r INmytest$# SELECT * FROM test WHERE id > 0mytest$# LOOPmytest$# RETURN NEXT r;mytest$# END LOOP;mytest$# RETURN;mytest$# ENDmytest$# $$ 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) |
返回某列
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mytest=# CREATE OR REPLACE FUNCTION test_0830_6(date) RETURNS SETOF integer AS $$mytest$# BEGINmytest$# RETURN QUERY SELECT idmytest$# FROM testmytest$# WHERE col2 >= $1mytest$# AND col2 < ($1 + 1);mytest$# IF NOT FOUND THENmytest$# RAISE EXCEPTION 'No id at %.', $1;mytest$# END IF;mytest$# RETURN;mytest$# ENDmytest$# $$mytest-# LANGUAGE plpgsql;CREATE FUNCTIONmytest=# select test_0830_6('2018-08-30');test_0830_6-------------2......11(10 rows) |
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/luojinbai/article/details/45487373
