我在postgres中使用表继承,但是我用来将数据分区到子表中的触发器的行为不太正确。例如,此查询返回nil,但我希望它返回id新记录的。
id
INSERT INTO flags (flaggable_id, flaggable_type) VALUES (233, 'Thank') RETURNING id;
如果将触发器函数的返回值从NULL更改为NEW,RETURNING则会得到所需的行为,但是随后在数据库中插入了两个相同的行。这是有道理的,因为来自触发器函数的非空返回值会导致原始INSERT语句执行,而返回NULL会导致语句停止执行。唯一索引可能会中止第二次插入,但可能会引发错误。
NULL
NEW
RETURNING
INSERT
有什么想法如何使INSERTwithRETURNING像这样的触发器正常工作吗?
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在该视图上使用触发器:
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。
flags_base
NEW.id
更新 :似乎视图的列也可以具有默认值,设置为
ALTER VIEW [如果存在] _ **名称**_ ALTER [COLUMN] _**column_name**_ 设置默认 _ **表达式**_
仅在视图中使用的具有插入/更新规则/触发器。
http://www.postgresql.org/docs/9.3/static/sql- alterview.html