PostgreSQL: function 返回结果集多列和单列的例子
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)); skytf=> insert into test_result1 select generate_series(1,10),'a'; skytf=> insert into test_result2 select generate_series(1,10),'b'; skytf=> select * From test_result1; skytf=> select * From test_result2; |
--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; |
--执行函数
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; |
--执行函数
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 会复杂很多。