postgresql - 带有 INSTEAD OF 触发器的 Postgres INSERT ON CONFLICT 行为

Question

假设我有以下表格和 View :

CREATE TABLE table_a(
    field_x INTEGER PRIMARY KEY,
    id SERIAL UNIQUE
);
CREATE TABLE table_b(
    a_id INTEGER PRIMARY KEY REFERENCES table_a(id),
    field_y INTEGER NOT NULL
);
CREATE VIEW v AS SELECT * FROM table_a JOIN table_b ON table_a.id=table_b.a_id;

我希望能够插入到 View 中，所以我创建了以下函数和触发器:

CREATE FUNCTION insert_into_view()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $function$
   DECLARE new_id INTEGER;
   BEGIN
      IF TG_OP = 'INSERT' THEN
        INSERT INTO table_a (field_x) VALUES (NEW.field_x) ON CONFLICT DO NOTHING RETURNING id INTO new_id;
        IF new_id IS NULL THEN
          SELECT id FROM table_a WHERE field_x=NEW.field_x INTO new_id;
        END IF;
        INSERT INTO table_b (a_id, field_y) VALUES (new_id, NEW.field_y);
      END IF;
      RETURN NEW;
    END;
$function$;
CREATE TRIGGER view_insert_trigger
    INSTEAD OF INSERT ON
      v FOR EACH ROW EXECUTE PROCEDURE insert_into_view();

现在我只想在 View 中不存在 field_x 的行时才将值插入 View ，例如:

INSERT INTO v (field_x, field_y) VALUES (5,6);
INSERT INTO v (field_x, field_y) VALUES (5,8) ON CONFLICT DO NOTHING;

我希望第二个插入静静地什么都不做。但是，我明白了:

ERROR:  duplicate key value violates unique constraint "table_b_pkey"
DETAIL:  Key (a_id)=(2) already exists.
CONTEXT:  SQL statement "INSERT INTO table_b (a_id, field_y) VALUES (new_id, NEW.field_y)"

我知道为什么会出现此错误:函数 insert_into_view 在插入 table_b 时未指定 ON CONFLICT 行为，默认情况下查询失败。因此我的问题是:我可以使 ON CONFLICT 行为从 View 插入波纹到表插入吗？ (我可能想在以后指定不同的冲突行为，所以如果可以避免的话，我不想在触发器函数中对其进行硬编码。)

谢谢!

Answer 1

我仍然不确定我是否理解正确。但我尝试:

如果你改变

INSERT INTO table_b (a_id, field_y) VALUES (new_id, NEW.field_y)

到

INSERT INTO table_b (a_id, field_y) VALUES (new_id, NEW.field_y) ON CONFLICT DO NOTHING

在函数中它将开始静默工作。

关于

INSERT INTO v (field_x, field_y) VALUES (5,8) ON CONFLICT DO NOTHING;

我认为您只能在具有唯一约束的表、so 和外部表上使用 ON CONFLICT，而规则将忽略 ON CONFLICT DO NOTHING 并在您指定时失败约束名称的目标名称