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

我無法讓 INSTEAD OF 觸發器正常工作，而且我想我誤解瞭如何使用 NEW。考慮以下簡化場景：

CREATE TABLE Product (
 product_id SERIAL PRIMARY KEY,
 product_name VARCHAR
);
CREATE TABLE Purchase (
 purchase_id SERIAL PRIMARY KEY,
 product_id INT REFERENCES Product,
 when_bought DATE
);

CREATE VIEW PurchaseView AS
SELECT purchase_id, product_name, when_bought
FROM Purchase LEFT JOIN Product USING (product_id);

我希望能夠創建INSTEAD OF觸發器以允許我直接插入PurchaseView，例如：

INSERT INTO Product(product_name) VALUES ('foo');
INSERT INTO PurchaseView(product_name, when_bought) VALUES ('foo', NOW());

我的想法是這樣的：

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
 RETURNS trigger AS
$BODY$
BEGIN
 INSERT INTO Purchase(product_id, when_bought)
 SELECT product_id, when_bought
 FROM NEW 
 LEFT JOIN Product USING (product_name)
 RETURNING * INTO NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER insert_productview_trig
 INSTEAD OF INSERT
 ON PurchaseView
 FOR EACH ROW
 EXECUTE PROCEDURE insert_purchaseview_func();

然而，上面的觸發函式relation "new" does not exist在執行時會給出錯誤（ ）。我知道我可以編寫顯式使用and子句NEW中的屬性的查詢，但有時能夠包含在連接中會很方便。有沒有辦法做到這一點？WHERE``SELECT``NEW

目前（不滿意）的解決方案

我能得到的最接近我想要的是

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
 RETURNS trigger AS
$BODY$
DECLARE
tmp RECORD;
BEGIN
   WITH input (product_name, when_bought) as (
      values (NEW.product_name, NEW.when_bought)
   ) 
   INSERT INTO Purchase(product_id, when_bought)
   SELECT product_id, when_bought
   FROM input
   LEFT JOIN Product USING (product_name)
   RETURNING * INTO tmp;
   RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

由於以下幾個原因，這有點令人不滿意：

1. 我需要NEW在 CTE WITH 查詢中顯式編寫 的所有屬性，這對於大視圖（尤其是那些屬性由 自動確定的視圖SELECT *）變得笨拙；
2. 返回的結果沒有更新SERIAL類型product_id，因此您沒有得到預期的結果：

INSERT INTO PurchaseView(product_name, when_bought) 
VALUES ('foo', NOW())
RETURNING *;

**NEW**是記錄，不是表格。基本：

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

稍作修改的設置

CREATE TABLE product (
 product_id serial PRIMARY KEY,
 product_name text UNIQUE NOT NULL  -- must be UNIQUE
);

CREATE TABLE purchase (
 purchase_id serial PRIMARY KEY,
 product_id  int REFERENCES product,
 when_bought date
);

CREATE VIEW purchaseview AS
SELECT pu.purchase_id, pr.product_name, pu.when_bought
FROM   purchase     pu
LEFT   JOIN product pr USING (product_id);

INSERT INTO product(product_name) VALUES ('foo');

product_name必須是UNIQUE，否則在該列上查找可能會找到多行，這會導致各種混亂。

1.簡單的解決方案

對於您的簡單範例，僅查找單個列product_id，低相關子查詢是最簡單和最快的：

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
 RETURNS trigger AS
$func$
BEGIN
  INSERT INTO purchase(product_id, when_bought)
  SELECT (SELECT product_id FROM product WHERE product_name = NEW.product_name), NEW.when_bought
  RETURNING purchase_id
  INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

  RETURN NEW;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER insert_productview_trig
INSTEAD OF INSERT ON purchaseview
FOR EACH ROW EXECUTE PROCEDURE insert_purchaseview_func();

沒有額外的變數。沒有 CTE（只會增加成本和噪音）。來自的列NEW僅拼寫一次（您的第 1 點）。

附加RETURNING purchase_id INTO NEW.purchase_id的會照顧您的第 2 點：現在，返回的行包括新生成的purchase_id.

如果未找到產品（NEW.product_name表中不存在product），則仍會插入購買並且product_id是NULL。這可能是可取的，也可能不是可取的。

2.

要跳過該行（並可能引發WARNING/ EXCEPTION）：

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
 RETURNS trigger AS
$func$
BEGIN
  INSERT INTO purchase AS pu
           (product_id,     when_bought)
  SELECT pr.product_id, NEW.when_bought
  FROM   product pr
  WHERE  pr.product_name = NEW.product_name
  RETURNING pu.purchase_id
  INTO   NEW.purchase_id;  -- generated serial ID for RETURNING - if needed

  IF NOT FOUND THEN  -- insert was canceled for missing product
     RAISE WARNING 'product_name % not found! Skipping INSERT.', quote_literal(NEW.product_name);
  END IF;

  RETURN NEW;
END
$func$  LANGUAGE plpgsql;

NEW這將列搭載到SELECT .. FROM product. 如果找到產品，一切都會正常進行。如果不是，則不會從 the 返回任何行，SELECT也不會發生任何INSERT事情。特殊的 PL/pgSQL 變數FOUND僅在最後一個 SQL 查詢至少處理了一行時才為真。

可以EXCEPTION代替WARNING引發錯誤並回滾事務。但我寧願無條件地聲明和插入（查詢 1 或類似的），效果相同：如果ispurchase.product_id NOT NULL引發異常。更簡單，更便宜。product_id``NULL

3.用於多次查找

CREATE OR REPLACE FUNCTION insert_purchaseview_func()
 RETURNS trigger AS
$func$
BEGIN
  INSERT INTO purchase AS pu
           (product_id,   when_bought)     -- more columns?
  SELECT pr.product_id, i.when_bought      -- more columns?
  FROM  (SELECT NEW.*) i                   -- see below
  LEFT   JOIN product  pr USING (product_name)
-- LEFT   JOIN tbl2     t2 USING (t2_name)  -- more lookups?
  RETURNING pu.purchase_id                 -- more columns?
  INTO   NEW.purchase_id;                  -- more columns?

  RETURN NEW;
END
$func$  LANGUAGE plpgsql;

LEFT JOINs 再次使無條件INSERT。JOIN如果找不到，請改為跳過。

FROM (SELECT NEW.*) i將記錄 NEW轉換為具有單行的*派生表**，*該表可以像FROM子句中的任何表一樣使用 - 最初是您要查找的。

db<>在這裡擺弄

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