在我的测试应用中,我有一个数据库,它在 PostgreSQL9.6
实例上设置如下。
CREATE TABLE public.parties
(
id integer NOT NULL DEFAULT nextval('parties_id_seq'::regclass),
party_type_id text NOT NULL,
fullname text NOT NULL DEFAULT ''::text,
created_at timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT parties_pkey PRIMARY KEY (id, party_type_id),
(... extra sql not relevant to the question ...)
);
CREATE TABLE public.party_people
(
id integer NOT NULL,
gender text NOT NULL DEFAULT ''::text,
CONSTRAINT party_people_pkey PRIMARY KEY (id),
(... extra sql not relevant to the question ...)
);
CREATE OR REPLACE VIEW public.people AS
SELECT t1.id,
t1.party_type_id,
t1.fullname,
t2.gender,
t1.created_at
FROM parties t1
JOIN party_people t2 ON t1.id = t2.id;
CREATE OR REPLACE FUNCTION public.people_vw_update_func()
RETURNS trigger AS
LANGUAGE plpgsql
$BODY$
BEGIN
IF TG_OP = 'INSERT' THEN
IF NEW.id IS NULL THEN
NEW.id = NEXTVAL('parties_id_seq');
END IF;
INSERT INTO parties
VALUES (NEW.id, NEW.party_type_id, NEW.fullname, NEW.created_at);
INSERT INTO party_people
VALUES (NEW.id, NEW.party_type_id, NEW.gender);
RETURN NEW;
ELSIF
(... extra sql to deal with DELETE and UPDATE cases ...)
END IF;
RETURN NEW;
END;
$BODY$
CREATE TRIGGER people_vw_update_trig
INSTEAD OF INSERT OR UPDATE OR DELETE
ON people
FOR EACH ROW
EXECUTE PROCEDURE people_vw_update_func();
我正在尝试在Postgres中创建一个可更新的视图,通过 people
视图管理个人数据,而不是手动编写查询来分割两个表中的数据。
我遇到的问题是,如果视图支持的表具有 NON NULL
约束,则无法执行以下查询:
INSERT INTO people (fullname, gender)
VALUES ("James Jones", "male");
NEW.create_at
在触发器函数中显然是 NULL
,因此由于对 created_at
的限制,将会失败。
我的问题是:
有没有人知道在可更新视图中处理 NON NULL
约束的方法,而不必采用以下方式:
IF NEW.created_at IS NULL THEN
INSERT INTO parties
VALUES (NEW.id, NEW.party_type_id, NEW.fullname);
ELSE
INSERT INTO parties
VALUES (NEW.id, NEW.party_type_id, NEW.fullname, NEW.created_at);
END IF;
虽然这个解决方案对于单列可能有效,但如果有更多的列,那么解决方案将变得非常混乱。
编辑
最终,我实施了Mad Scientist推荐的解决方案。对于未来可能遇到这个问题的任何人,我的最终解决方案是:
ALTER TABLE people
ALTER COLUMN created_at SET DEFAULT now()
因此,在视图触发器中,我只需要在适当的表中设置值,people 视图将负责填充 NEW
变量的默认值,其中通常会出现 NULL
。