在 Postgres 觸發器的 FROM 子句中使用 NEW？

我正在嘗試編寫一個 Postgres 觸發器以在插入或更新新行之前取消嵌套數組欄位。例如

SELECT
unnest(something)
FROM NEW

但是，這似乎會導致錯誤：

關係“新”不存在

如何在觸發器函式中使用 NEW 行，以允許我取消嵌套數組欄位以進行進一步處理？

創建表語句

以下是表結構的範例：

CREATE TABLE parent_table (
   id uuid NOT NULL,
   jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],
   CONSTRAINT "parent_table_pkey" PRIMARY KEY (id),
);

CREATE TABLE many_to_one_table (
 id serial primary key,
 parent_table_id uuid references parent_table(id),
 subfield_a text,
 subfield_a text
);

觸發功能

這裡是TRIGGER函式的本質：

CREATE OR REPLACE FUNCTION unnest_and_normalize_json() RETURNS TRIGGER AS 
$body$
begin
   if NEW.jsonb_array_field is null then
       raise exception 'jsonb_array_field is null';
   else
       insert into 
           many_to_one_table(subfield_a, subfield_b)
           select
               parent_table_id, -- this is to be the ForeignKey
               json_data->>'subfieldA' as subfield_a,
               json_data->>'subfieldB' as subfield_b
           from (
               select 
                   id, -- need ID for ForeignKey relationship
                   unnest(jsonb_array_field) as json_data
                   from new
           ) as unnested_json;
   end if;
   RETURN new;
end;
$body$ 
LANGUAGE plpgsql;

觸發語句

觸發器語句可以在 INSERT 和 UPDATE 之前或之後執行，只要數據在“遷移”表中鏡像即可。

CREATE TRIGGER unnest_and_normalize_json_on_insert AFTER INSERT ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

CREATE TRIGGER unnest_and_normalize_json_on_update AFTER UPDATE ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

ForeignKey 說明 我們正在嘗試將數據模型的這一部分轉換為使用 ForeignKey 關係而不是 JSON 欄位。觸發器旨在作為一個臨時步驟，以確保normalized_table有數據向前發展，同時我們從舊記錄中回填/遷移數據。

NEW並且OLD在觸發器函式中是記錄- 每個只在適用的情況下定義。（例如，觸發器中沒有OLD。INSERT）您可以對它們執行ROW操作。使用點表示法（如表限定的列名）引用嵌套列，就像它們是列表中包含的FROM表一樣。NEW並且OLD在觸發器函式的 SQL DML 語句中幾乎隨處可見。（帶 . 的動態 SQL 除外EXECUTE。）

要將NEW其視為實際表，您必須首先對其進行轉換 - 這通常不是必需的。喜歡：(SELECT NEW.*)。有關有意義的案例，請參閱第3章。在此相關答案中：

• PostgreSQL 在查詢 INSTEAD OF 觸發器時使用 NEW

您的基本觸發功能可以歸結為：

CREATE OR REPLACE FUNCTION unnest_and_normalize_json()
 RETURNS trigger AS 
$func$
BEGIN
  INSERT INTO many_to_one_table (parent_table_id, subfield_a, subfield_b)
  SELECT NEW.id
       , ja->>'subfieldA' AS subfield_a  -- column alias only for documentation
       , ja->>'subfieldB'
  FROM   unnest(NEW.jsonb_array_field) ja;  -- produces a derived table

  RETURN NEW; -- optional for AFTER trigger
END
$func$  LANGUAGE plpgsql;

這只是噪音：

if NEW.jsonb_array_field is null then ...

該列jsonb_array_field定義為：

jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],

這是一個AFTER觸發器，此時NOT NULL約束已經引發了NULLin異常jsonb_array_field。

您可以將兩個觸發器合併為一個：

CREATE TRIGGER unnest_and_normalize_json_on_insert
AFTER INSERT OR UPDATE ON parent_table  -- !
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

也就是說，您必須做更多的工作才能UPDATE正確覆蓋DELETE。類似於這個（黑暗面一章）：

• PostgreSQL 可以對數組元素有唯一性約束嗎？

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