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