PostgreSQL 11 新特性解讀: 支援儲存過程(SQL Stored Procedures)

PostgreSQL SQL · 發表 2018-11-17 12:23:05

摘要： PostgreSQL 11 版本一個重量級新特性是對儲存過程的支援，同時支援儲存過程嵌入事務，儲存過程是很多 PostgreSQL 從業者期待已久的特性，尤其是很多從Oracle轉到PostgreSQL朋友，儘管PostgreSQL提供函式可以實現大多數儲存過程的功能，但在函式中無法執行事務實...

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 ] [, ...] )

儲存過程呼叫比較簡單，使用 CALL 命令即可，而函式的呼叫是使用 SELECT 命令。

儲存過程嵌入事務測試

建立一個簡單的儲存過程演示下。

建立測試表

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都已提交。

建立儲存過程 ins_t1_part

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 表的資料沒有提交，驗證了儲存過程支援嵌入式事務。

參考

ofollow,noindex" target="_blank">CREATE PROCEDURE
TECH PREVIEW: POSTGRESQL 11 – CREATE PROCEDURE

新書推薦

最後推薦和張文升共同編寫的《PostgreSQL實戰》，本書基於PostgreSQL 10 編寫，共18章，重點介紹SQL高階特性、並行查詢、分割槽表、物理複製、邏輯複製、備份恢復、高可用、效能優化、PostGIS等，涵蓋大量實戰用例！

購買連結： https://item.jd.com/12405774.html