PostgreSQL: function 返回结果集多列和单列的例子

2012-06-23 13:10:21| 分类： Postgres基础 | 标签： |字号大中小订阅

今天有人问到在 PostgreSQL 函数中如何返回结果集的单列，返回结果集（多列）的方法很多，
那么如何返回结果集的单列呢，做了下测试，具体步骤如下:

一测试一：返回多条记录(单列)
--1 创建测试表并插入记录

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)

--2 方法一：返回多条记录（单列）

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;

--执行函数

skytf=> SELECT * FROM func_test_result_single(1) ;
func_test_result_single
-------------------------
b
a
(2 rows)

--3 方法二：使用 reutrn query 返回多条记录（单列）

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;

--执行函数

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" 返回多条记录（多列）

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;

--执行函数

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 返回多条记录（多列）

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;

--执行函数

skytf=> SELECT * FROM func_test_result_query(1) t(id integer,name varchar);
id | name
----+------
1 | a
1 | b
(2 rows)

--2.3 使用 out 输出参数返回多条记录（多列）

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;

--执行函数

skytf=> select skytf.func_test_result_out(1);
func_test_result_out
----------------------
(1,a)
(1,b)
(2 rows)

三总结：

以上只是为了演示 PostgreSQL 函数的语法给出简单的例子，生产过程中的 function 会复杂很多。