特定例外處理與訊息

PostgreSQL 的交易與錯誤處理

Jason Myers

Principal Engineer

捕捉特定型別的例外

DO $$
BEGIN
    UPDATE inventory SET quantity = quantity - 1 WHERE name in ('flour', 'sugar');
EXCEPTION
    WHEN check_violation THEN
           INSERT INTO errors (msg) VALUES ('Quantity can not be less than 0.');
           RAISE INFO 'Quantity can not be less than 0.';
END; 
$$ language 'plpgsql';

我們的例外處理輸出

INFO:  Quantity can not be less than 0.
DO
postgres=# select * from errors;
 error_id | state |               msg                | detail | context
^---------+-------+----------------------------------+--------+---------
        1 |       | Quantity can not be less than 0. |        |
(1 row)

常見的例外條件類型

條件名稱	範例
unique_violation	在唯一欄位插入兩筆相同值
not_null_violation	在不允許 null 的欄位插入 null
check_violation	違反檢查條件，例如「美味度」必須大於 11
division_by_zero	以 0 為除數

更多類型請見下方引文連結

¹ https://www.postgresql.org/docs/9.4/errcodes-appendix.html

同時捕捉多個例外

DO $$
BEGIN
    UPDATE inventory SET quantity = quantity - 6, cost = null 
    WHERE name='oatmeal dark chocolate';

分別捕捉多種例外型別

-- 新增 check_violation 例外
EXCEPTION
  WHEN check_violation THEN
     INSERT INTO errors (msg) VALUES ('Quantity can not be less than 0.');
     RAISE INFO 'Quantity can not be less than 0.';

-- 新增 not_null 例外
  WHEN not_null_violation THEN
     INSERT INTO errors (msg) VALUES ('Cost can not be null.');
     RAISE INFO 'Cost can not be null.';
END; $$ language 'plpgsql';

同時捕捉多個例外的輸出

INFO:  Cost can not be null.
DO
postgres=# select * from errors;
 error_id | state |               msg                | detail | context
^---------+-------+----------------------------------+--------+---------
        2 |       | Cost can not be null.            |        |
(1 row)

動手套用吧！

PostgreSQL 的交易與錯誤處理