PostgreSQL 11 新特性之存储过程

PostgreSQL 11 版本一个重量级新特性是对存储过程的支持，同时支持存储过程嵌入事务，存储过程是很多 PostgreSQL 从业者期待已久的特性，尤其是很多从Oracle转到PostgreSQL朋友，尽管PostgreSQL提供函数可以实现大多数存储过程的功能，但在函数中无法执行事务实现部分提交，换句话说，函数中的SQL要么都执行成功，要不全部返回失败。
PostgreSQL 11 版本对存储过程的支持，从兼容层面考虑和Oracle的兼容性进一步增强，本文演示下PostgreSQL存储过程的创建和调用，并且演示存储过程支持嵌入事务样例。

存储过程创建语法

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  }

存储过程调用语法CALL name ( [ argument ] [, ...] )

存储过程调用比较简单，使用 CALL 命令即可，而函数的调用是使用 SELECT 命令。

优势

存储过程最大的优势就是能够支持事务控制，也就是可以在定义中使用 COMMIT 或者 ROLLBACK 语句。
使用 CREATE\ALTER\DROP PROCEDURE 命令创建\修改\删除存储过程，使用 CALL 命令调用存储过程。支持存储过程的服务器端编程语言包括：PL/pgSQL、PL/Perl、PL/Python、PL/Tcl 以及 SPI 。

列子

CREATE TABLE tbl(id int, name text);

CREATE PROCEDURE proc1(pid integer, pname text)
LANGUAGE sql
AS $$
INSERT INTO tbl(id, name) VALUES (pid, pname);
$$;

使用 CALL 语句调用存储过程：

CALL proc1(1, 'the first value');
CALL proc1(pname=>'the second value', pid=>2);

查看一下结果：

SELECT * FROM tbl;
 id |       name       
----+------------------
  1 | the first value
  2 | the second value
(2 rows)

存储过程增加了对事务的支持：

CREATE OR REPLACE PROCEDURE proc2()
 LANGUAGE plpgsql
AS $$
BEGIN
 FOR i IN 0..9 LOOP
  INSERT INTO tbl(id, name) VALUES (i, 'value: '|| i);
  IF i % 2 = 0 THEN
   COMMIT;
  ELSE
   ROLLBACK;
  END IF;
 END LOOP;
END;
$$;

调用存储过程 proc2，即使没有参数，仍然需要加上括号（()）：

CALL proc2();

查看结果：

SELECT * FROM tbl;
 id |       name       
----+------------------
  1 | the first value
  2 | the second value
  0 | value: 0
  2 | value: 2
  4 | value: 4
  6 | value: 6
  8 | value: 8
(7 rows)

只有id 为偶数的记录成功插入表中，奇数都被 ROLLBACK 语句回退了。

在 psql 中，使用 \df 命令查看存储过程：

\df
                         List of functions
 Schema | Name  | Result data type |   Argument data types   | Type 
--------+-------+------------------+-------------------------+------
 public | add   | integer          | integer, integer        | func
 public | proc1 |                  | pid integer, pname text | proc
 public | proc2 |                  |                         | proc
(3 rows)

对于存储过程，Type 的值为 proc。使用 \sf 命令可以查看存储过程的定义：

\sf proc1
CREATE OR REPLACE PROCEDURE public.proc1(pid integer, pname text)
 LANGUAGE sql
AS $procedure$
INSERT INTO tbl(id, name) VALUES (pid, pname);
$procedure$

PROCDEURE 目前还不支持自治事务（autonomous transaction）。