PostgreSQL函数如何返回数据集

概述转自：http://my.oschina.net/Kenyon/blog/108303 以下主要介绍PostgreSQL函数/存储过程返回数据集，或者也叫结果集的示例。  背景： PostgreSQL里面没有存储过程，只有函数，其他数据库里的这两个对象在PG里都叫函数。 函数由函数头，体和语言所组成，函数头主要是函数的定义，变量的定义等，函数体主要是函数的实现，函数的语言是指该函数实现的方式，目前

转自：http://my.oschina.net/Kenyon/blog/108303

以下主要介绍Postgresql函数/存储过程返回数据集，或者也叫结果集的示例。

背景： 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 $$beginreturn 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$$declarerec dept_salary%rowtype;beginfor 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$$declarerec record;beginfor rec in EXECUTE 'select * from ' ||  loopreturn 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://bbs.pgsqldb.com/clIEnt/post_show.PHP?zt_auto_bh=53950 总结

以上是内存溢出为你收集整理的PostgreSQL函数如何返回数据集全部内容，希望文章能够帮你解决PostgreSQL函数如何返回数据集所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。