主页 / dba / 问题 / 144197
Accepted
beldaz
Asked: 2016-07-19 05:54:58 +0800 CST

PostgreSQL 在查询 INSTEAD OF 触发器时使用 NEW

  • 772

我无法让 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中的属性的查询，但有时能够包含在连接中会很方便。有没有办法做到这一点？WHERESELECTNEW

当前（不满意）的解决方案

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

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 *;
postgresql trigger

2 个回答

  1. Best Answer

    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_idNULL

    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<>在这里摆弄

    • 11

  2. 正如评论中所建议的，看起来我能做的最接近我想要的是（修复我在问题中的原始方法）：

    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 purchase_id INTO tmp;
    NEW.purchase_id = tmp.purchase_id;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

    这至少使该RETURNING子句正常工作。看起来NEW必须显式声明的属性。以下：

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

    ERROR: column "product_name" specified in USING clause does not exist in left table触发触发器时的结果。

    • 4

相关问题