postgresql 基于pl/sql的function

最帅dba工作笔记 2021-04-22

905

介绍

　　PL/pgSQL 是PostgreSQL 数据库系统的一个可加载的过程语言。PL/pgSQL 的设计目标是创建一种可加载的过程语言，可以用于创建函数和触发器过程, 为SQL 语言增加控制结构, 执行复杂的计算继承所有用户定义类型、函数、操作符, 定义为被服务器信任的语言。PL/pgSQL创建的函数可以在那些使用内置函数一样的情形下使用。比如，可以创建复杂的条件计算函数，并随后将之用于定义操作符或者用于函数索引中。

使用PL/pgSQL的优点

SQL是PostgreSQL和大多数其它关系型数据库的命令语言。
它是可移植的并且容易学习使用。但是所有SQL语句都必须由数据库服务器独立地执行.
通过PL/pgSQL，可以把运算块和一系列命令在数据库服务器内部组成一个块，这样就拥有了过程语言的能力并且简化 SQL 的使用，因而节约了大量的时间，因为不需要进行客户端/服务器通讯。
消除了服务器和客户端之间的往返通信。
客户端不需要的中间结果无需在服务器端和客户端来回传递。
不需要额外的语法分析步骤。比起不使用存储函数来，这样做能够产生明显的性能提升

根据上一个文章的例子，可以模拟一个pl/pgsql的例子：

create or replace function pl_pg_fun(a numeric,b numeric)
returns numeric as $total$
declare
    total numeric;
begin
    total := a+b;
    raise notice '%+%=%',a,b,total;             --这里是一个标识，在查询结果中会出现一个提示
    return total;
end;
$total$ language plpgsql;
复制

调用结果

vastbase=# select pl_pg_fun(1,3);
NOTICE:  1+3=4                                              
CONTEXT:  referenced column: pl_pg_fun          --这两行就是raise notice '%+%=%',a,b,total 提示的显示结果
pl_pg_fun
-----------
         4
(1 row)
复制

块结构（类似于Oracle匿名块）

DECLARE
   --声明部分: 在此声明PL/SQL用到的变量,类型及游标.
BEGIN
   -- 执行部分:  过程及SQL语句,即程序的主要部分
EXCEPTION
   -- 执行异常部分: 错误处理
END;
复制

PL/pgsql是一个块结构语言，函数定义的所有文本都必须是一个块。

其中：

执行部分不能省略。
块中的每一个declaration和每一条statement都由一个分号终止
块支持嵌套，嵌套时子块的END后面必须跟一个分号，最外层的块END后可不跟分号
BEGIN后面不必也不能跟分号，END后跟的label名必须和块开始时的标签名一致
所有关键字都不区分大小写。标识符被隐含地转换成小写字符，除非被双引号包围
声明的变量在当前块及其子块中有效，子块开始前可声明并覆盖（只在子块内覆盖）外部块的同名变量（就是说子块的变量可以覆盖父块的变量，并且只在子块内生效）
变量被子块中声明的变量覆盖时，子块可以通过外部块的label访问外部块的变量

变量的声明：

同oracle procedure 一样变量的声明在declare 下，使用的赋值方法是 := 防止和sql语句中的= 混淆

name [constant] type [not null] [default.....约束限制]
复制

例如：

name text := 'vastbase';    
age  numeric :=25;                 --上面两个是赋值
school varchar;       --只是声明变量，等待后面操作
复制

变量命名规则如下：

标识符名不能超过30字符；
第一个字符必须为字母；
不分大小写；
不能用’-‘(减号);
不能是SQL保留字。

参数

参数的指定方法如下

[in|out|in out]     name   typy     --in 输入，out输出
复制

在声明参数的name的时候，其实这个name也就是一个别名，可以直接用在begin的执行块中引用。

至于这个inout，官方也没有指出是什么时候用到，我自己实验了一下，感觉应该是可以指定其输入，返回输出的类型和该参数的类型相同。

create or replace function new_fun(in a numeric,inout b numeric)
as $$
select a+b;
$$ language sql;
复制

结果

vastbase=# select new_fun(1,2);
new_fun
---------
       3
(1 row)
复制

参数也可以不指定名字，只写数据类型，在begin后面的执行部分中就可以使用 $1 ,$2 ....$n 去指定对应位置的参数。这个和sql function是一样的。

示例

create or replace function new_fun1( numeric)
returns numeric as  $$
begin
return $1 * 2;
end;
$$ language plpgsql;
复制

结果

vastbase=# select new_fun1(20);
new_fun1
----------
       40
(1 row)
复制

还有一种语法是在参数部分没有指定数据类型，直接指定了name（这里的意思是别名），则在Declare中可以声明变量的时候把变量指定为参数的别名。

示例：

create or replace function new_fun2( numeric)
returns numeric as  $$
declare
  num1  alias for $1;      
begin
return num1 * 2;
end;
$$ language plpgsql;
复制

结果：

vastbase=# select new_fun2(2);
new_fun2
----------
        4
(1 row)
复制

oracle 的procedure 和 pg的function 对比：

oracle ：

DECLARE
  quantity number := 30;
BEGIN
  -- Prints 30
  DBMS_OUTPUT.PUT_LINE('Quantity here is ' || quantity);
  quantity := 50;
  -- Create a subblock
    DECLARE
      quantity number := 80;
    BEGIN
      -- Prints 80
      DBMS_OUTPUT.PUT_LINE('Quantity here is ' || quantity);
    END;
    -- Prints 50
  DBMS_OUTPUT.PUT_LINE('Quantity here is ' || quantity);
END;
复制

postgresql：

CREATE FUNCTION somefunc() RETURNS integer AS $$
DECLARE
    quantity integer := 30;
BEGIN
    -- Prints 30
    RAISE NOTICE 'Quantity here is %', quantity;
    quantity := 50;
    -- Create a subblock
    DECLARE
        quantity integer := 80;
    BEGIN
        -- Prints 80
        RAISE NOTICE 'Quantity here is %', quantity;
    END;
    -- Prints 50
    RAISE NOTICE 'Quantity here is %', quantity;
    RETURN quantity;
END;
$$ LANGUAGE plpgsql;
复制

THAT'S ALL

BY CUI PEACE

postgresql

文章转载自最帅dba工作笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

postgresql 基于pl/sql的function

评论

相关阅读