我在postgres中使用表继承,但是我用来将数据分区到子表中的触发器并不是很正常.例如,此查询返回nil,但我希望它返回新记录的id.
INSERT INTO flags (flaggable_id,flaggable_type) VALUES (233,'Thank') RETURNING id;
如果我将触发器函数的返回值从NULL更改为NEW,我会得到所需的RETURNING行为,但随后会在数据库中插入两个相同的行.这是有道理的,因为来自触发器函数的非null返回值导致原始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'),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触发器：
CREATE TABLE flags_base (
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"="%",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).
更新：看来,视图的列也可以有一个默认值,设置为
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
仅在视图中使用的具有插入/更新规则/触发器.
http://www.postgresql.org/docs/9.3/static/sql-alterview.html
