優雅地測試 PostgreSQL 語法

使用 EXPLAIN 指令來「假執行」

在某些應用場景之中，查詢語法是動態產生或臨場指定的。這種時候可能需要事先檢查語法是否符合語法。最直接的方式當然是直接執行，但只能在執行環境才能運作。一個比較簡便的方式，是使用 EXPLAIN 指令來「假執行」。

EXPLAIN 的功能用於產生執行計劃，如果你已經在執行環境之中，那麼 EXPLAIN 成功，即代表能夠正常執行的條件都已完備，語法本身沒有問題；而如果 EXPLAIN 失敗，則可以依顯示訊息，瞭解錯誤的原因為何，再進行修正。

使用 EXPLAIN 還有一個較為重要的需求是，不會觸發任何 TRIGGER 或其他衍生的執行動作，這能夠確保資料安全。

實際測試的情況可能如下：

postgres=# explain select * from not_exist_table;
ERROR:  relation "not_exist_table" does not exist
LINE 1: explain select * from not_exist_table;
                              ^

使用者可以看到錯誤的原因，但對於程式的處理就不太方便了。事實上 PostgreSQL 對於這些錯誤資訊有明確的定義：

• https://www.postgresql.org/docs/current/errcodes-appendix.html

可以查到錯誤代碼(成功為 00000)，及其簡單的描述名稱。

綜合以上，一個簡易的函式，也許可以幫上一點忙：

-- SELECT sqlcheck('select * from not_exist_table');
-- 42P01: undefined_table
-- SELECT sqlcheck('select * from not_exist_table wrong_syntax');
-- 42601: syntax_error
-- 語法檢查會優先於物件檢查

CREATE OR REPLACE FUNCTION sqlcheck(query text) RETURNS text AS
$$
DECLARE
 sql text;
 code text;
BEGIN
 code='00000';
 sql='EXPLAIN '||query;
 EXECUTE sql;
 RETURN code;

 EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS
   code=RETURNED_SQLSTATE;
   RETURN code;
END;
$$ language 'plpgsql';

sqlcheck 簡單地把 EXCEPTION 的行為轉換成回傳值，方便你事先建立好程式處理的流程，而不會被中斷。

當你的測試環境和執行環境不同時，主要是測試 42601，也就是語法錯誤的部份。想像一下 EXPLAIN 的流程，首先是檢查語法，然後確認物件，再來才能產生查詢計畫。所以如果同時有許多錯誤時，語法錯誤會先被拋出。

物件檢查的錯誤，如果不在執行環境的話，出錯是必然，可以視實際情況略過。

有時候語法是帶有參數的，一點小技巧是，可以先把變數代換為 null，雖然對資料面而言並不一定是合理的，但可以順利在語法上做到可以檢查的基礎。

另一種可能的應用場景是，進行資料庫遷移作業時，事先針對已記錄的原資料庫查詢語法，只要先行匯入進資料庫，就能大量確認語法是否相容，並且分類好錯誤的原因，以利於擬定查詢語法的移轉計畫。

https://flic.kr/p/D8qzp2