PostgreSQL 11 版本一个重量级新特性是对存储过程的支持,同时支持存储过程嵌入事务,存储过程是很多 PostgreSQL 从业者期待已久的特性,尤其是很多从Oracle转到PostgreSQL朋友,尽管PostgreSQL提供函数可以实现大多数存储过程的功能,但在函数中无法执行事务实现部分提交,换句话说,函数中的SQL要么都执行成功,要不全部返回失败。
PostgreSQL 11 版本对存储过程的支持,从兼容层面考虑和Oracle的兼容性进一步增强,本文演示下PostgreSQL存储过程的创建和调用,并且演示存储过程支持嵌入事务样例。
发行说明
SQL stored procedures, with support for embedded transactions
存储过程创建语法
1
2
3
4
5
6
7
8
9
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 命令。
存储过程嵌入事务测试
创建一个简单的存储过程演示下。
创建测试表
CREATE TABLE t1 (id int4);
CREATE TABLE t1_log (ctime timestamp(6) without time zone , operation text,ins_values int4);
创建存储过程 ins_t1
1
2
3
4
5
6
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(),如下
1
2
francs=> CALL ins_t1(1);
CALL
查看表 t1 和 t1_log 记录。
1
2
3
4
5
6
7
8
9
10
11
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都已提交。
创建存储过程 ins_t1_part
1
2
3
4
5
6
7
8
9
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(),如下
1
2
francs=> CALL ins_t1_part(2);
CALL
查看表 t1 和 t1_log 记录。
1
2
3
4
5
6
7
8
9
10
11
12
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 表的数据没有提交,验证了存储过程支持嵌入式事务。
参考
CREATE PROCEDURE
TECH PREVIEW: POSTGRESQL 11 – CREATE PROCEDURE
最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!