**
(PG数据库函数写法（存储过程）)
**
以下主要介绍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
s
e
l
e
c
t
∗
f
r
o
m
e
m
p
l
o
y
e
e
;
select * from employee;
select∗fromemployee;
language ‘sql’;
等同的另一个效果(Query)
create or replace function f_get_employee_query()
returns setof employee
as
b
e
g
i
n
r
e
t
u
r
n
q
u
e
r
y
s
e
l
e
c
t
∗
f
r
o
m
e
m
p
l
o
y
e
e
;
e
n
d
;
begin return query select * from employee; end;
beginreturnqueryselect∗fromemployee;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,30000,1)
(2,francs,50000,1)
(3,digoal,60000,2)
(4,narutu,120000,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
d
e
c
l
a
r
e
r
e
c
d
e
p
t
s
a
l
a
r
y
b
e
g
i
n
f
o
r
r
e
c
i
n
s
e
l
e
c
t
d
e
p
a
r
t
m
e
n
t
i
d
,
s
u
m
(
s
a
l
a
r
y
)
a
s
t
o
t
a
l
s
a
l
a
r
y
f
r
o
m
f
g
e
t
e
m
p
l
o
y
e
e
(
)
g
r
o
u
p
b
y
d
e
p
a
r
t
m
e
n
t
i
d
l
o
o
p
r
e
t
u
r
n
n
e
x
t
r
e
c
;
e
n
d
l
o
o
p
;
r
e
t
u
r
n
;
e
n
d
;
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;
declarerecdeptsalarybeginforrecinselectdepartmentid,sum(salary)astotalsalaryfromfgetemployee()groupbydepartmentidloopreturnnextrec;endloop;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
d
e
c
l
a
r
e
v
r
e
c
r
e
c
o
r
d
;
b
e
g
i
n
f
o
r
v
r
e
c
i
n
s
e
l
e
c
t
d
e
p
a
r
t
m
e
n
t
i
d
a
s
d
e
p
t
i
d
,
s
u
m
(
s
a
l
a
r
y
)
a
s
t
o
t
a
l
s
a
l
a
r
y
f
r
o
m
f
g
e
t
e
m
p
l
o
y
e
e
(
)
g
r
o
u
p
b
y
d
e
p
a
r
t
m
e
n
t
i
d
l
o
o
p
o
d
e
p
t
:
=
v
r
e
c
.
d
e
p
t
i
d
;
o
s
a
l
a
r
y
:
=
v
r
e
c
.
t
o
t
a
l
s
a
l
a
r
y
;
r
e
t
u
r
n
n
e
x
t
;
e
n
d
l
o
o
p
;
e
n
d
;
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;
declarevrecrecord;beginforvrecinselectdepartmentidasdeptid,sum(salary)astotalsalaryfromfgetemployee()groupbydepartmentidloopodept:=vrec.deptid;osalary:=vrec.totalsalary;returnnext;endloop;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
KaTeX parse error: Can't use function '$' in math mode at position 67: …ct * from ' || $̲1 loop return n…
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
3521
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
