作者介绍：

谭峰 网名francs，中国开源软件推进联盟PostgreSQL分会特聘专家，《PostgreSQL实战》作者之一，《PostgreSQL 9 Administration Cookbook》译者之一，致力于PostgreSQL技术分享，博客 https://postgres.fun

PostgreSQL 11 版本一个重量级新特性是对存储过程的支持，同时支持存储过程嵌入事务，存储过程是很多 PostgreSQL 从业者期待已久的特性，尤其是很多从Oracle转到PostgreSQL朋友，尽管PostgreSQL提供函数可以实现大多数存储过程的功能，但在函数中无法执行事务实现部分提交，换句话说，函数中的SQL要么都执行成功，要不全部返回失败。

PostgreSQL 11 版本对存储过程的支持，从兼容层面考虑和Oracle的兼容性进一步增强，本文演示下PostgreSQL存储过程的创建和调用，并且演示存储过程支持嵌入事务样例。

发行说明

SQL stored procedures, with support for embedded transactions

存储过程创建语法

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 ] [, ...] )存储过程调用比较简单，使用

存储过程嵌入事务测试

创建一个简单的存储过程演示下。

创建测试表

CREATE TABLE t1 (id int4);CREATE TABLE t1_log (ctime timestamp(6) without time zone , operation text,ins_values int4);

创建存储过程 ins_t1

CREATE OR REPLACE PROCEDURE ins_t1(a integer) AS $$BEGIN      INSERT INTO t1(id) VALUES(a);      INSERT INTO t1_log(ctime,operation,ins_values) VALUES (clock_timestamp(),'INSERT',a);END$$ LANGUAGE 'plpgsql';

调用存储过程 ins_t1()，如下

francs=> CALL ins_t1(1);CALL

查看表 t1 和 t1_log 记录。

francs=> SELECT * FROM t1; id----  1(1 row)francs=> SELECT * FROM t1_log;           ctime            | operation | ins_values----------------------------+-----------+------------ 2018-09-25 15:08:16.026122 | INSERT    |          1(1 row)

以上看出两条SQL都已提交。

创建存储过程 inst1part

CREATE OR REPLACE PROCEDURE ins_t1_part(a integer) AS $$BEGIN      INSERT INTO t1(id) VALUES(a);      COMMIT;       INSERT INTO t1_log(ctime,operation,ins_values) VALUES (clock_timestamp(),'INSERT',a);      ROLLBACK;END$$ LANGUAGE 'plpgsql';

调用存储过程ins_t1_part()，如下

francs=> CALL ins_t1_part(2);CALL

查看表t1和t1_log记录。

francs=> SELECT * FROM t1; id----  1  2(2 rows)francs=> SELECT * FROM t1_log;           ctime            | operation | ins_values----------------------------+-----------+------------ 2018-09-25 15:08:16.026122 | INSERT    |          1(1 row)

发现t1表的数据已提交，而t1_log表的数据没有提交，验证了存储过程支持嵌入式事务。

参考

1.CREATE PROCEDUR

2.TECH PREVIEW: POSTGRESQL 11 – CREATE PROCEDUR