如何在视图上编写删除规则？

Question

我正在尝试在视图上编写一条规则以从组件表中删除元组，但到目前为止只能从其中之一删除数据。我已经使用带有基本视图的 postgres 一段时间了，但是我对视图规则没有任何经验。

我写了一个愚蠢的小测试用例来找出/展示我的问题。在此示例中，每个子元组只有一个父元组（当然，我的实际架构实际上并非如此）。

组件表：

CREATE TABLE parent(
   id serial PRIMARY KEY,
   p_data integer NOT NULL UNIQUE
);
CREATE TABLE child(
   id serial PRIMARY KEY,
   parent_id integer NOT NULL UNIQUE REFERENCES parent(id),
   c_data integer NOT NULL
);

视图：

CREATE TABLE child_view(
   id integer,
   p_data integer,
   c_data integer
);
CREATE RULE "_RETURN" AS ON SELECT TO child_view DO INSTEAD
   SELECT child.id, p_data, c_data 
      FROM parent JOIN child ON (parent_id=parent.id);

问题删除规则

CREATE RULE child_delete AS ON DELETE TO child_view DO INSTEAD(
   DELETE FROM child WHERE id=OLD.id;
   DELETE FROM parent WHERE p_data=OLD.p_data;
);

上述规则的目的是从组件表中删除视图中引用的元组。 WHERE p_data=OLD.p_data 对我来说似乎很奇怪，但我不知道如何在父表中引用所需的元组。

当我尝试使用上述规则时，会发生以下情况：

>SELECT * FROM child_view;
 id | p_data | c_data 
----+--------+--------
  1 |      1 |     10
  2 |      2 |     11
  3 |      3 |     12
(3 rows)

>DELETE FROM child_view WHERE id=3;
DELETE 0

>SELECT * FROM child_view;
 id | p_data | c_data 
----+--------+--------
  1 |      1 |     10
  2 |      2 |     11
(2 rows)

但是查看父表，删除的第二部分不起作用（id=3“应该”已被删除）：

>SELECT * FROM parent;
 id | p_data 
----+--------
  1 |      1
  2 |      2
  3 |      3
(3 rows)

我应该如何编写删除规则来删除子元组和父元组？

这是使用 postgres v9。

任何帮助表示赞赏。另外，还可以指出任何涵盖 postgres 文档之外的视图规则的材料（除非我明显遗漏了某些内容）。谢谢。

编辑：正如 jmz 指出的那样，使用级联删除比这里的规则更容易，但这种方法不适用于我的实际模式。

Answer 1

您所看到的规则问题是规则系统不能自动处理数据。无论 DO INSTEAD 规则中两条语句的顺序如何，都会执行第一个删除。第二条语句永远不会执行，因为 OLD.id 引用的行已从视图中删除。您可以使用 LEFT JOIN，但这对您没有帮助，因为示例表设计（它可能适用于您的实际数据库架构）。

在我看来，根本问题是您将规则系统视为触发器。

您最好的选择是使用外键和ON DELETE CASCADE而不是规则。有了它们，您的示例模式也可以工作：您只需要删除父表即可删除所有子表。

Answer 2

你想做的事情将会很好地进行。但你却左转了：

CREATE TABLE child_view(
   id integer,
   p_data integer,
   c_data integer
);
CREATE RULE "_RETURN" AS ON SELECT TO child_view DO INSTEAD
   SELECT child.id, p_data, c_data 
      FROM parent JOIN child ON (parent_id=parent.id);

你想要的是这里的现实生活景观而不是桌子。这就是为什么删除不起作用。

CREATE VIEW child_view AS SELECT
    child.id,
    p_data,
    c_data
    FROM parent
        JOIN child ON (parent_id=parent.id)
;

将顶部替换为底部，它将完美工作（我测试时确实如此）。删除不起作用的原因是它试图从 TABLE 子视图中删除 id，而该子视图当然是空的！它不执行“选择执行替代”规则，因此它正在处理真实的表子视图。人们可能会使用规则来拉便，但如果他们看不到如此明显的错误，我想知道他们知道多少？

我已经成功地使用规则来定义接口来执行业务规则。他们可以以触发器无法做到的方式引导优雅的解决方案。

注意：我只建议这样做来为界面创建可写视图。您可以做一些聪明的事情，例如检查表之间的约束 - 并且您可能要求这样做。这种东西确实应该与触发器一起使用。

编辑：每个请求的脚本

-- set this as you may have had an error if you running 
-- from a script and not noticed it with all the NOTICES
\set ON_ERROR_STOP

drop table if exists parent cascade;
drop table if exists child cascade;

CREATE TABLE parent(
        id serial PRIMARY KEY,
        p_data integer NOT NULL UNIQUE
);

CREATE TABLE child(
        id serial PRIMARY KEY,
        parent_id integer NOT NULL UNIQUE REFERENCES parent(id),
        c_data integer NOT NULL
);

        CREATE VIEW child_view AS SELECT
                child.id,
                p_data,
                c_data
                FROM parent 
                        JOIN child ON (parent_id=parent.id)
        ;

CREATE RULE child_delete AS ON DELETE TO child_view DO INSTEAD(
        DELETE FROM child WHERE id=OLD.id;
        DELETE FROM parent WHERE p_data=OLD.p_data;
);

insert into parent (p_data) values (1), (2), (3);
insert into child (parent_id, c_data) values (1, 1), (2, 2), (3, 3);

select * from child_view;

 id | p_data | c_data 
----+--------+--------
  1 |      1 |      1
  2 |      2 |      2
  3 |      3 |      3
(3 rows)

delete from child_view where id=3;
DELETE 0

select * from child_view;
 id | p_data | c_data 
----+--------+--------
  1 |      1 |      1
  2 |      2 |      2
(2 rows)