PostgreSQL可更新视图与非空约束

Question

在我的测试应用中，我有一个数据库，它在 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。

- Zerodestiny

Answer 1

您不仅可以在表格上创建默认值，还可以在视图上创建默认值。如果您有可更新的视图，则可以将底层表格的默认值添加到其中。 NOT NULL约束可以由底层表强制执行，但是如果未提供值，则INSTEAD OF触发器将看到默认值，而不是问题NULL。

视图上默认值的语法与更改表格默认值的语法相同。

- Mad Scientist

Answer 2

您只需要做：

IF NEW.created_at IS NULL THEN
    NEW.created_at = now() ;
END IF ;
/* Do the same for all columns requiring default values */

INSERT INTO 
       parties 
       (id,     party_type_id,     fullname,     created_at)
VALUES 
       (NEW.id, NEW.party_type_id, NEW.fullname, NEW.created_at);

或者也可以。

INSERT INTO 
       parties 
       (id,     party_type_id,     fullname,     created_at)
VALUES 
       (NEW.id, NEW.party_type_id, NEW.fullname, coalesce(NEW.created_at, now()));

请查看Coalesce文档。

- joanolo

---

推荐使用coalesce函数，这是我正在寻找的几乎完美的解决方案。尽管最终我选择了Mad Scientists的答案，因为我认为在视图上设置默认值更适合我的目的。 - Zerodestiny