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

返回数据集（也称为结果集）的 PostgreSQL 函数/存储过程示例。

背景：PostgreSQL中没有存储过程，只有函数。这两个对象在其他数据库中称为PG中的函数。函数由函数头、函数体和语言组成。函数头主要是函数的定义、变量的定义等，函数体主要是函数的实现。函数的语言是指函数的实现方式。目前内置的有c、plpgsql、sql和internal，可以通过pg_language查看当前DB支持的语言，也可以扩展支持python等。

函数返回值​​一般都是类型，如返回int、varchar、返回结果集express时需要setof。 ？查询，但是如果查询方式不同，结果就会不同。下面的查询方法会产生类似数组的效果

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 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)

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