postgresql - 如何编写嵌套的 PostgreSQL 规则？

Question

在 Postgresql 9.4 中运行以下简单规则时:

create table a1 (id int);
create table a2 (id int);
create table a3 (id int);
create view a0 as select * from a1;

insert into a1 values (1), (2), (3);
insert into a2 select * from a1;
insert into a3 select * from a1;

Create or replace rule a0_delete as
on delete to a0 do instead (
    delete from a1 where id = old.id;
    delete from a2 where id = old.id;
    delete from a3 where id = old.id;
    select a1.id, a2.id, a3.id
    from a1 
    left join a2 using(id)
    left join a3 using(id);
);

delete from a0 where id = 2;

第一次运行后我无法进行任何操作，我也不知道为什么。尽管文档 http://www.postgresql.org/docs/9.4/static/rules-update.html指定这是可能的，我在任何地方都找不到包含多个操作的示例。

有人知道吗？

Answer 1

多命令规则完美运行。你可以试试这个脚本:

create table a1 (id int);
create table a2 (id int);
create table a3 (id int);

insert into a1 values (1), (2), (3);
insert into a2 select * from a1;
insert into a3 select * from a1;

create or replace rule a1_delete as
on delete to a1 do instead (
    insert into a1 values (old.id* 2);
    delete from a2 where id = old.id;
    delete from a3 where id = old.id;
    select a1.id, a2.id, a3.id
    from a1 
    left join a2 using(id)
    left join a3 using(id);
);

delete from a1 where id = 2;

获得预期结果:

CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 3
INSERT 0 3
CREATE RULE
 id | id | id 
----+----+----
  1 |  1 |  1
  2 |    |   
  3 |  3 |  3
  4 |    |   
(4 rows)

DELETE 1

您应该在规则的第二个命令中查找逻辑错误。