sql - Postgres 基于触发器的插入重定向而不中断 RETURNING

Question

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

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

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

关于如何使带有 RETURNING 的 INSERT 与这样的触发器一起正常工作，有什么想法吗？

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();

Answer 1

我发现的唯一解决方法是为基表创建一个 View 并在该 View 上使用 INSTEAD OF 触发器:

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)。

更新:似乎 View 的列也可以有默认值，设置为

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

仅在具有插入/更新规则/触发器的 View 中使用。

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