PostgreSQL函数如何返回数据集

落日映苍穹つ 2022-08-26 03:42 13阅读 0赞

以下主要介绍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 
    $$
    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,30000,1)
     (2,francs,50000,1)
     (3,digoal,60000,2)
     (4,narutu,120000,3)
    (4 rows)

因为返回的结果集类似一个表的数据集，PostgreSQL还支持对该函数执行结果进行条件判断并过滤

上面的例子相对简单，如果要返回不是表结构的数据集该怎么办呢？看下面   
  
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';

执行结果：

这样同一个函数就可以返回不同的结果集了，很灵活。

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