PostgreSQL函数/存储过程返回数据集(结果集)的示例

2019-03-3020:12:17数据库教程PostgreSQL函数/存储过程返回数据集(结果集)的示例已关闭评论7,965 views字数 3147阅读模式

PostgreSQL函数/存储过程返回数据集,或者也叫结果集的示例。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

背景: PostgreSQL里面没有存储过程,只有函数,其他数据库里的这两个对象在PG里都叫函数。 函数由函数头,体和语言所组成,函数头主要是函数的定义,变量的定义等,函数体主要是函数的实现,函数的语言是指该函数实现的方式,目前内置的有c,plpgsql,sql和internal,可以通过pg_language来查看当前DB支持的语言,也可以通过扩展来支持python等文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

函数返回值一般是类型,比如return int,varchar,返回结果集时就需要setof来表示。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

一、数据准备文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

createtable department(id intprimarykey, name text);
createtable employee(id intprimarykey, name text, salary int, departmentid intreferences department);

insertinto department values (1, 'Management'),(2, 'IT'),(3, 'BOSS');

insertinto employee values (1, 'kenyon', 30000, 1);
insertinto employee values (2, 'francs', 50000, 1);
insertinto employee values (3, 'digoal', 60000, 2);
insertinto employee values (4, 'narutu', 120000, 3);

二、例子
一例文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

createorreplacefunction f_get_employee() 
returns setof employee 
as 
$$
select*from employee;
$$
language 'sql';

等同的另一个效果(Query)文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

createorreplacefunction f_get_employee_query() 
returns setof employee 
as 
$$
beginreturn query select*from employee;
end;
$$
language plpgsql;

查询图解如下文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

postgres=# select*from f_get_employee();
 id |  name  | salary | departmentid 
----+--------+--------+--------------1| kenyon |30000|12| francs |50000|13| digoal |60000|24| narutu |120000|3
(4 rows)

查询出来的函数还可以像普通的表一样按条件查询 ,但如果查询的方式不一样,则结果也不一样,以下查询方式将会得到类似数组的效果文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

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还支持对该函数执行结果进行条件判断并过滤文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

postgres=# select*from f_get_employee() where id >3;
 id |  name  | salary | departmentid 
----+--------+--------+--------------4| narutu |120000|3
(1 row)

上面的例子相对简单,如果要返回不是表结构的数据集该怎么办呢?看下面文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

2.返回指定结果集文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

--a.用新建type来构造返回的结果集--新建的type在有些图形化工具界面中可能看不到,
--要查找的话可以通过select * from pg_class where relkind='c'去查,c表示composite typecreate type dept_salary as (departmentid int, totalsalary int);

createorreplacefunction f_dept_salary() 
returns setof dept_salary 
as
$$
declare
rec dept_salary%rowtype;
beginfor rec inselect departmentid, sum(salary) as totalsalary from f_get_employee() groupby departmentid loop
  returnnext rec;
  end loop;
return;
end;
$$
language 'plpgsql';

--b.用Out传出的方式createorreplacefunction f_dept_salary_out(out o_dept text,out o_salary text) 
returns setof record as
$$
declare
    v_rec record;
beginfor v_rec inselect departmentid as dept_id, sum(salary) as total_salary from f_get_employee() groupby departmentid loop
        o_dept:=v_rec.dept_id;
        o_salary:=v_rec.total_salary;  
        returnnext;
    end loop; 
end;
$$
language plpgsql;
--执行结果:
postgres=# select*from f_dept_salary();
 departmentid | totalsalary 
--------------+-------------1|800003|1200002|60000
(3 rows)

postgres=# select*from f_dept_salary_out();
 o_dept | o_salary 
--------+----------1|800003|1200002|60000
(3 rows)

--c.根据执行函数变量不同返回不同数据集createorreplacefunction f_get_rows(text) returns setof record as
$$
declare
rec record;
beginfor rec inEXECUTE'select * from '|| $1 loop
returnnext 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|12| francs        |50000|13| digoal        |60000|24| narutu        |120000|3
(4 rows)

这样同一个函数就可以返回不同的结果集了,很灵活。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/10843.html

  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/sjk/10843.html