CODEWITHSUNDEEP
postgresql
Tudor
Tudor

Reputation: 173

How can I write nested PostgreSQL rules?

While running the following simple rule in 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;

I cannot make any action after the first run, and I don't know why. Although the documentation http://www.postgresql.org/docs/9.4/static/rules-update.html specifies that it is possible, I couldn't find anywhere an example with more than one action.

Does anyone has any idea?

Upvotes: 3

Views: 198

Answers (1)

klin
klin

Reputation: 121754

Multiple-command rules work flawlessly. You can try this script:

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;

to get expected results:

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

You should look for a logical error in the second command of your rule.

Upvotes: 1

Related Questions