Postgresql：插入觸發器功能在部分插入語句上失敗

我們的數據庫中有一些大表，老闆認為分區對我們的系統是一個很好的改進。我負責調查它並在測試數據庫中開發所有需要的腳本，然後向其他人解釋一切。我不是 dba，我是 perl 開發人員。

只關註一個主表，我製作了一個函式來創建一個新的子表，為現有資訊創建子表，並批量將東西從主表移動到子表。我認為這無關緊要，但我們檢查的是創建欄位的年份和月份。

現在我正在主數據庫中建構插入觸發器，它將檢測新記錄的周期，查找相應的子表，如果失去則創建它，然後插入。一切準備就緒，但插入不起作用！

我們的插入語句通常是部分的：將添加基本欄位，做一些事情並填寫其餘部分（填寫部分稍後會出現）。所以插入語句類似於

INSERT INTO public.transactions ( created, tr_status )
VALUES (now(), 'created');

並留下 Postgres 填寫 id。但在 和 之間created，tr_status模式有一個名為 modified 的欄位。而且，當數據到達觸發函式時，該欄位為空。嘗試插入該行將​​引發錯誤：

EXECUTE (
   'INSERT INTO ' || child || ' VALUES ' || NEW.* 
);

LINE 1: ...15_07 VALUES (123456,"2015-07-03 16:48:17.890627",,completed...
                                                             ^

如何在觸發器中使用插入語句中的相同欄位？

試試這個表格：

EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW;

它至少需要 PostgreSQL 8.4，但現在應該淘汰以前的版本。

一個更現代、更簡潔的版本（如有必要，請引用表的名稱）：

EXECUTE format('insert into %I values ($1.*)', child) USING NEW;

您已經發現必須將動態 SQL 與EXECUTE. 你缺少什麼：

• 如果不能保證子表共享相同的行類型，則必須在語句中添加目標列列表，INSERT否則必然會遇到錯誤或更糟：它可能會以令人驚訝的方式工作。
• 您需要防禦 SQL 注入。表名必須被視為潛在危險的使用者輸入。
• 不要連接值，將它們作為值與USING子句一起傳遞。

EXECUTE (format('
  INSERT INTO %I(created, modified, tr_status, ...)
  VALUES (($1).created, ($1).modified, ($1).tr_status, ...)'
  , child)
USING NEW;

或者，對於許多列：

EXECUTE (format('
  INSERT INTO %I(created, modified, tr_status, ...)
  SELECT created, modified, tr_status, ...
  FROM  (SELECT ($1).*) t'
  , child)
USING NEW;

有關的：

• 更新plpgsql中觸發器函式中的多個列
• 僅給定欄位名稱，如何訪問 NEW 或 OLD 欄位？

引用自：https://dba.stackexchange.com/questions/105883