以下主要介绍PostgreSQL函数/存储过程返回数据集，或者也叫结果集的示例。
背景： PostgreSQL里面没有存储过程，只有函数，其他数据库里的这两个对象在PG里都叫函数。 函数由函数头，体和语言所组成，函数头主要是函数的定义，变量的定义等，函数体主要是函数的实现，函数的语言是指该函数实现的方式，目前内置的有c,plpgsql,sql和internal,可以通过pg_language来查看当前DB支持的语言，也可以通过扩展来支持python等
函数返回值一般是类型，比如return int,varchar，返回结果集时就需要setof来表示。
一、数据准备
1create table department(id int primary key, name text); 2create table employee(id int primary key, name text, salary int, departmentid int references department); 3 4insert into department values (1, 'Management'),(2, 'IT'),(3, 'BOSS'); 5 6insert into employee values (1, 'kenyon', 30000, 1); 7insert into employee values (2, 'francs', 50000, 1); 8insert into employee values (3, 'digoal', 60000, 2); 9insert into employee values (4, 'narutu', 120000, 3);
二、例子
1.sql一例
1create or replace function f_get_employee() 2returns setof employee 3as 4$$ 5select * from employee; 6$$ 7language 'sql';
等同的另一个效果(Query)
1create or replace function f_get_employee_query() 2returns setof employee 3as 4$$ 5begin 6return query select * from employee; 7end; 8$$ 9language plpgsql;
查询图解如下
1postgres=# select * from f_get_employee(); 2 id | name | salary | departmentid 3----+--------+--------+-------------- 4 1 | kenyon | 30000 | 1 5 2 | francs | 50000 | 1 6 3 | digoal | 60000 | 2 7 4 | narutu | 120000 | 3 8(4 rows)
查询出来的函数还可以像普通的表一样按条件查询 ,但如果查询的方式不一样，则结果也不一样，以下查询方式将会得到类似数组的效果
1postgres=# select f_get_employee(); 2 f_get_employee 3--------------------- 4 (1,kenyon,30000,1) 5 (2,francs,50000,1) 6 (3,digoal,60000,2) 7 (4,narutu,120000,3) 8(4 rows)
因为返回的结果集类似一个表的数据集，PostgreSQL还支持对该函数执行结果进行条件判断并过滤
1postgres=# select * from f_get_employee() where id >3; 2 id | name | salary | departmentid 3----+--------+--------+-------------- 4 4 | narutu | 120000 | 3 5(1 row)
上面的例子相对简单，如果要返回不是表结构的数据集该怎么办呢？看下面
2.返回指定结果集
a.用新建type来构造返回的结果集
--新建的type在有些图形化工具界面中可能看不到，
要查找的话可以通过select * from pg_class where relkind='c'去查，c表示composite type
1create type dept_salary as (departmentid int, totalsalary int); 2 3create or replace function f_dept_salary() 4returns setof dept_salary 5as 6$$ 7declare 8rec dept_salary%rowtype; 9begin 10for rec in select departmentid, sum(salary) as totalsalary from f_get_employee() group by departmentid loop 11 return next rec; 12 end loop; 13return; 14end; 15$$ 16language 'plpgsql';
b.用Out传出的方式
1create or replace function f_dept_salary_out(out o_dept text,out o_salary text) 2returns setof record as 3$$ 4declare 5 v_rec record; 6begin 7 for v_rec in select departmentid as dept_id, sum(salary) as total_salary from f_get_employee() group by departmentid loop 8 o_dept:=v_rec.dept_id; 9 o_salary:=v_rec.total_salary; 10 return next; 11 end loop; 12end; 13$$ 14language plpgsql;
执行结果:
1postgres=# select * from f_dept_salary(); 2 departmentid | totalsalary 3--------------+------------- 4 1 | 80000 5 3 | 120000 6 2 | 60000 7(3 rows) 8 9postgres=# select * from f_dept_salary_out(); 10 o_dept | o_salary 11--------+---------- 12 1 | 80000 13 3 | 120000 14 2 | 60000 15(3 rows)
c.根据执行函数变量不同返回不同数据集
1create or replace function f_get_rows(text) returns setof record as 2$$ 3declare 4rec record; 5begin 6for rec in EXECUTE 'select * from ' || $1 loop 7return next rec; 8end loop; 9return; 10end 11$$ 12language 'plpgsql';
执行结果：
1postgres=# select * from f_get_rows('department') as dept(deptid int, deptname text); 2 deptid | deptname 3--------+------------ 4 1 | Management 5 2 | IT 6 3 | BOSS 7(3 rows) 8 9postgres=# select * from f_get_rows('employee') as employee(employee_id int, employee_name text,employee_salary int,dept_id int); 10 employee_id | employee_name | employee_salary | dept_id 11-------------+---------------+-----------------+--------- 12 1 | kenyon | 30000 | 1 13 2 | francs | 50000 | 1 14 3 | digoal | 60000 | 2 15 4 | narutu | 120000 | 3 16(4 rows)
这样同一个函数就可以返回不同的结果集了，很灵活。
参考：http://bbs.pgsqldb.com/client/post\_show.php?zt\_auto\_bh=53950