今天有人问到在 PostgreSQL 函数中如何返回结果集的单列，返回结果集（多列）的方法很多，那么如何返回结果集的单列呢，做了下测试，具体步骤如下:
测试一: 返回多条记录（单列）
1.1 创建测试表并插入记录
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
31
32
33
34
35
36
37
38
|
skytf=> create table test_result1 (id integer,name varchar(32));
CREATE TABLE
skytf=> create table test_result2 (id integer,name varchar(32));
CREATE TABLE
skytf=> insert into test_result1 select generate_series(1,10),'a';
INSERT 0 10
skytf=> insert into test_result2 select generate_series(1,10),'b';
INSERT 0 10
skytf=> select * From test_result1;
id | name
----+------
1 | a
2 | a
3 | a
4 | a
5 | a
6 | a
7 | a
8 | a
9 | a
10 | a
(10 rows)
skytf=> select * From test_result2;
id | name
----+------
1 | b
2 | b
3 | b
4 | b
5 | b
6 | b
7 | b
8 | b
9 | b
10 | b
(10 rows)
|
1.2 方法一：返回多条记录（ 单列）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE OR REPLACE FUNCTION skytf.func_test_result_single ( in_id integer)
RETURNS SETOF varchar as
$$
DECLARE
v_name varchar;
BEGIN
for v_name in ( (select name from test_result1 where id = in_id) union (select name from test_result2 where id = in_id) )loop
RETURN NEXT v_name;
end loop;
return;
END;
$$
LANGUAGE PLPGSQL;
|
执行函数
1
2
3
4
5
6
|
skytf=> SELECT * FROM func_test_result_single(1)
func_test_result_single
-------------------------
b
a
(2 rows)
|
1.3 方法二：使用 reutrn query 返回多条记录（ 单列）
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE OR REPLACE FUNCTION skytf.func_test_result_query_single ( in_id integer)
RETURNS SETOF varchar as
$$
DECLARE
v_rec RECORD;
BEGIN
return query ( (select name from test_result1 where id = in_id) union (select name from test_result2 where id = in_id) );
return;
END;
$$
LANGUAGE PLPGSQL;
|
执行函数
1
2
3
4
5
6
|
skytf=> select func_test_result_query_single (1)
func_test_result_query_single
-------------------------------
b
a
(2 rows)
|
备注： 在返回指定 SETOF varchar 返回 varchar 类型单个字段，接下来介绍下返回多条记录多列的场景。
测试二: 返回多条记录（多列）
2.1 使用游标和”RETURNS SETOF RECORD” 返回多条记录（ 多列）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE OR REPLACE FUNCTION skytf.func_test_result_muti ( in_id integer)
RETURNS SETOF RECORD as
$$
DECLARE
v_rec RECORD;
BEGIN
for v_rec in ( (select id , name from test_result1 where id = in_id) union (select id , name from test_result2 where id = in_id) )loop
RETURN NEXT v_rec;
end loop;
return;
END;
$$
LANGUAGE PLPGSQL;
|
执行函数
1
2
3
4
5
6
|
skytf=> SELECT * FROM func_test_result_muti(1) t(id integer,name varchar)
id | name
----+------
1 | a
1 | b
(2 rows)
|
2.2 使用 reutrn query 返回多条记录（ 多列）
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE OR REPLACE FUNCTION skytf.func_test_result_query ( in_id integer)
RETURNS SETOF RECORD as
$$
DECLARE
v_rec RECORD;
BEGIN
return query ( (select id , name from test_result1 where id = in_id) union (select id , name from test_result2 where id = in_id) );
return;
END;
$$
LANGUAGE PLPGSQL;
|
执行函数
1
2
3
4
5
6
|
skytf=> SELECT * FROM func_test_result_query(1) t(id integer,name varchar)
id | name
----+------
1 | a
1 | b
(2 rows)
|
2.3 使用 out 输出参数 返回多条记录（ 多列）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
CREATE OR REPLACE FUNCTION skytf.func_test_result_out ( in_id integer,out o_id integer,out o_name varchar)
RETURNS SETOF RECORD as
$$
DECLARE
v_rec RECORD;
BEGIN
for v_rec in ( (select id , name from test_result1 where id = in_id) union (select id , name from test_result2 where id = in_id) )loop
o_id := v_rec.id;
o_name := v_rec.name;
RETURN NEXT ;
end loop;
return;
END;
$$
LANGUAGE PLPGSQL;
|
执行函数
1
2
3
4
5
6
|
skytf=> select skytf.func_test_result_out(1);
func_test_result_out
----------------------
(1,a)
(1,b)
(2 rows)
|
总结
以上只是为了演示 PostgreSQL 函数的语法给出简单的例子，生产过程中的 function 会复杂很多。
原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/237872.html
