文档章节

PostgreSQL函数如何返回数据集

kenyon_君羊
 kenyon_君羊
发布于 2013/02/07 11:10
字数 845
阅读 18445
收藏 56
以下主要介绍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还支持对该函数执行结果进行条件判断并过滤
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
$$
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';
执行结果:
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之MVCC
kenyon_君羊
粉丝 500
博文 170
码字总数 121714
作品 0
杭州
其他
私信 提问
加载中

评论(14)

253451295
253451295
create FUNCTION [dbo].[S_IsFitSize]
(
  @x money,
  @Xstr nvarchar(100) --(21,2000]或者(301,]等等
)
RETURNS int --返回1表示成功,返回0表示失败. 即T中型号不在型号表中
AS
BEGIN
  declare @str nvarchar(100)
  declare @v int
  declare @bFit int
  declare @T1 int,@T2 int,@t int
  declare @bFitX int,@bFitY int
  declare @LeftChar nvarchar(10)
  declare @RightChar nvarchar(10)
  declare @i int
  declare @s nvarchar(50)
  --现在求Xstr的范围
  set @str=@Xstr
  set @v=@x
  set @bfit=0
  set @str=LTrim(@str)
  set @str=RTrim(@str)
  if (@str is null or @str='') set @bfit=1
  else
  begin
    set @LeftChar=Left(@str ,1)
    set @RightChar=Right(@str ,1)
    if @LeftChar in ('(','[') and @RightChar in (')',']')
    begin
      set @str=SUBSTRING(@str ,2,len(@str)-2)
      --现在处理 1,200 或者 ,200 或者200,这三种正常的情况
      set @str=LTrim(@str)
      set @str=RTrim(@str)
      set @t=charindex(',',@str)
      if @t=1 --如果是 ,200这样的情况,
      begin
        set @str=Right(@str ,Len(@str)-1)
        if @str is not null and IsNumeric(@str)=1
        begin
          set @T2=@str
          if (@v<@T2 and @RightChar=')')
253451295
253451295
各位我有个sql 函数转换 pg函数,请大家帮忙
create FUNCTION dbo.S_IsStrMatchFieldStr (
@mstr nvarchar(100), --注意,这两个参数都可以为空
@FieldStr nvarchar(1600)
)
RETURNS int AS
BEGIN
  declare @fei int --为1表示@FieldStr以非字开头,为0则不是
  declare @r int
  declare @str nvarchar(100)
  declare @found int
  declare @Len int
  
  set @FieldStr=REPLACE(@FieldStr,' ','') --去掉所有空格
  set @FieldStr=REPLACE(@FieldStr,',',',') --全角逗号替换为半角的
  set @mstr=REPLACE(@mstr,' ','') --去掉所有空格
  if (@FieldStr is NULL) or (@FieldStr='') return 1 --条件字段为NULL,表示任意@mstr都允许
  
  if (@FieldStr='空') --如果条件字段中有一个汉字"空",表示@mstr为空时才算满足条件
  begin
    if (@mstr is NULL) or (@mstr='') return 1
    else  return 0
  end  
  if @mstr is null or @mstr='' return 0
  if (@FieldStr='非空') --如果条件字段中有一个汉字"非空",表示@mstr为非空时才算满足条件
  begin
    if (@mstr is NULL) or (@mstr='') return 0
    else  return 1
  end
  if Left(@FieldStr,1)='非'
  begin
    set @fei=1
    set @FieldStr=Right(@FieldStr,Len(@FieldStr)-1) --去掉非字再判断
  end else
    set @fei=0
  
  set @FieldStr=','+@FieldStr+',' --左右两边加上逗号好开始查找
mark35
mark35
“因为没开js” -- 是 “以为noscript没允许js”
mark35
mark35

引用来自“宏哥”的评论

引用来自“kenyon(君羊)”的评论

引用来自“mark35”的评论

多种返回类型,很实用

水了....

haha, mysql 死锁的问题, 所以他刷新了多次。

第一次是ctrl+enter提交,没反应,因为没开js。于是手工点击‘回复’来提交,还是没反应。刷新一次再提交,出现一串json字符串,明白提交成功了,也知道会复读了~
宏哥
宏哥

引用来自“kenyon(君羊)”的评论

引用来自“mark35”的评论

多种返回类型,很实用

水了....

haha, mysql 死锁的问题, 所以他刷新了多次。
kenyon_君羊
kenyon_君羊 博主

引用来自“mark35”的评论

多种返回类型,很实用

水了....
mark35
mark35
多种返回类型,很实用
mark35
mark35
多种返回类型,很实用
mark35
mark35
多种返回类型,很实用
宏哥
宏哥
很好. 我最近就在用这个功能.

函数返回结果集比起View有很多优势.
PostgreSQL函数如何返回数据集

以下主要介绍PostgreSQL函数/存储过程返回数据集,或者也叫结果集的示例。 背景: PostgreSQL里面没有存储过程,只有函数,其他数据库里的这两个对象在PG里都叫函数。 函数由函数头,体和语言...

永远的java
2013/10/29
0
0
将你的网站从MySQL改为PostgreSQL

原著:Nathan Matias 翻译:处处 我的站点最初是采用PHP驱动,由MySQL数据库支持的方案,这在当时是一个明智的方案。在2001年夏天,我将我的数据库换成了PostgreSQL(有时也简称为Postgres)。...

李昊
2012/07/14
4K
11
‘PostgreSQL用户会’携手‘机工社华章公司’发起以下赠书活动

活动参与方式: QQ空间分享此帖:并将分享截图发布到此帖下面,即有机会获赠精美图书; 活动时间:11.12—11.19 活动奖品: 《PostgreSQL服务器编程》,总10本。结束后,由机工社华章公司统一...

YuanyuanL
2014/11/12
348
2
Postgres中的WAL(三)ControlFileData & CKPT

控制文件在数据库中非常的重要,里面会记录CKPT信息以及数据库的初始化信息。这篇文件主要分享一下控制文件,以及数据库崩溃的情况下如何如何通过控制文件和CKPT来回放WAL。 PG_VERSION:10....

RDBMS原理分析
2018/07/12
0
0
PostgreSQL 批量SQL before/after for each row trigger的触发时机、性能差异分析、建议 - 含9.4 , 10版本

标签 PostgreSQL , trigger , row , statement , before , after , s_lock 背景 数据库触发器的触发时机,性能,高并发批量导入时,触发器的性能如何? 批量导入时,before, after触发器在f...

德哥
2018/10/05
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Mybatis Plus删除

/** @author beth @data 2019-10-17 00:30 */ @RunWith(SpringRunner.class) @SpringBootTest public class DeleteTest { @Autowired private UserInfoMapper userInfoMapper; /** 根据id删除......

一个yuanbeth
今天
4
0
总结

一、设计模式 简单工厂:一个简单而且比较杂的工厂,可以创建任何对象给你 复杂工厂:先创建一种基础类型的工厂接口,然后各自集成实现这个接口,但是每个工厂都是这个基础类的扩展分类,spr...

BobwithB
今天
5
0
java内存模型

前言 Java作为一种面向对象的,跨平台语言,其对象、内存等一直是比较难的知识点。而且很多概念的名称看起来又那么相似,很多人会傻傻分不清楚。比如本文我们要讨论的JVM内存结构、Java内存模...

ls_cherish
今天
4
0
友元函数强制转换

友元函数强制转换 p522

天王盖地虎626
昨天
5
0
js中实现页面跳转(返回前一页、后一页)

本文转载于:专业的前端网站➸js中实现页面跳转(返回前一页、后一页) 一:JS 重载页面,本地刷新,返回上一页 复制代码代码如下: <a href="javascript:history.go(-1)">返回上一页</a> <a h...

前端老手
昨天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部