基于Postgres触发器的插入重定向而不会破坏RETURNING [英] Postgres trigger-based insert redirection without breaking RETURNING

问题描述

我在postgres中使用表继承，但是我用来将数据分区到子表中的触发器的表现不太正确。例如，此查询返回nil，但我希望它返回新记录的 id 。

I'm using table inheritance in postgres, but the trigger I'm using to partition data into the child tables isn't quite behaving right. For example, this query returns nil, but I would like it to return the id of the new record.

INSERT INTO flags (flaggable_id, flaggable_type) 
VALUES (233, 'Thank') 
RETURNING id;

如果我将触发函数的返回值从 NULL 到 NEW ，我得到所需的 RETURNING 行为，但是随后在数据库中插入了两个相同的行。这是有道理的，因为触发函数的非空返回值会导致原始 INSERT 语句执行，而返回NULL会导致语句暂停执行。唯一索引可能会中止第二次插入，但可能会引发错误。

If I change the return value of the trigger function from NULL to NEW, I get the desired RETURNING behavior, but then two identical rows are inserted in the database. This makes sense, since a non-null return value from the trigger function causes the original INSERT statement execute, whereas returning NULL causes the statement to halt execution. A unique index might halt the second insertion, but would probably raise an error.

任何想法如何制作 INSERT 与返回一起使用这样的触发器可以正常工作吗？

Any ideas how to make the INSERT with RETURNING work properly with a trigger like this?

CREATE TABLE flags (
  id integer NOT NULL,
  flaggable_type character varying(255) NOT NULL,
  flaggable_id integer NOT NULL,
  body text
);

ALTER TABLE ONLY flags
    ADD CONSTRAINT flags_pkey PRIMARY KEY (id);

CREATE TABLE "comment_flags" (
 CHECK ("flaggable_type" = 'Comment'),
 PRIMARY KEY ("id"),
 FOREIGN KEY ("flaggable_id") REFERENCES "comments"("id")
) INHERITS ("flags");

CREATE TABLE "profile_flags" (
 CHECK ("flaggable_type" = 'Profile'),
 PRIMARY KEY ("id"),
 FOREIGN KEY ("flaggable_id") REFERENCES "profiles"("id")
) INHERITS ("flags");

CREATE OR REPLACE FUNCTION flag_insert_trigger_fun() RETURNS TRIGGER AS $BODY$
BEGIN
  IF (NEW."flaggable_type" = 'Comment') THEN
    INSERT INTO comment_flags VALUES (NEW.*);
  ELSIF (NEW."flaggable_type" = 'Profile') THEN
    INSERT INTO profile_flags VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'Wrong "flaggable_type"="%", fix flag_insert_trigger_fun() function', NEW."flaggable_type";
  END IF;
  RETURN NULL;
END; $BODY$ LANGUAGE plpgsql;

CREATE TRIGGER flag_insert_trigger
  BEFORE INSERT ON flags
  FOR EACH ROW EXECUTE PROCEDURE flag_insert_trigger_fun();

推荐答案

我发现的唯一解决方法是创建视图用于基本表&在该视图上使用 INSTEAD OF 触发器：

The only workaround I found, is to create a view for the base table & use INSTEAD OF triggers on that view:

CREATE TABLE flags_base (
    id integer NOT NULL,
    flaggable_type character varying(255) NOT NULL,
    flaggable_id integer NOT NULL,
    body text
);

ALTER TABLE ONLY flags_base
    ADD CONSTRAINT flags_base_pkey PRIMARY KEY (id);

CREATE TABLE "comment_flags" (
 CHECK ("flaggable_type" = 'Comment'),
 PRIMARY KEY ("id")
) INHERITS ("flags_base");

CREATE TABLE "profile_flags" (
 CHECK ("flaggable_type" = 'Profile'),
 PRIMARY KEY ("id")
) INHERITS ("flags_base");

CREATE OR REPLACE VIEW flags AS SELECT * FROM flags_base;

CREATE OR REPLACE FUNCTION flag_insert_trigger_fun() RETURNS TRIGGER AS $BODY$
BEGIN
  IF (NEW."flaggable_type" = 'Comment') THEN
    INSERT INTO comment_flags VALUES (NEW.*);
  ELSIF (NEW."flaggable_type" = 'Profile') THEN
    INSERT INTO profile_flags VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'Wrong "flaggable_type"="%", fix flag_insert_trigger_fun() function', NEW."flaggable_type";
  END IF;
  RETURN NEW;
END; $BODY$ LANGUAGE plpgsql;

CREATE TRIGGER flag_insert_trigger
  INSTEAD OF INSERT ON flags
  FOR EACH ROW EXECUTE PROCEDURE flag_insert_trigger_fun();

但是这种方式您必须提供 id每次插入字段（即使 flags_base 的主键具有默认值/是一个序列），因此您必须准备插入触发器以修复 NEW.id 如果它是 NULL 。

But this way you must supply the id field on each insertion (even if flags_base's primary key has a default value / is a serial), so you must prepare your insert trigger to fix NEW.id if it is a NULL.

更新：似乎视图列也可以具有默认值，并设置为

UPDATE: It seems views' columns can have a default values too, set with

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression

仅用于具有插入/更新规则/触发器的视图。

which is only used in views have an insert/update rule/trigger.

http://www.postgresql.org/docs/ 9.3 / static / sql-alterview.html

这篇关于基于Postgres触发器的插入重定向而不会破坏RETURNING的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！