Thread: create rule with multiple commands

select version();

begin;

create table _a (id serial primary key, value int not null);

create table deleted (id serial primary key, tid int not null);

create view a as select a.id, a.value from _a a left join deleted s on s.tid = a.id where s.id is null;

create rule a_insert as on insert to a do instead insert into _a (value) values (new.value);

create rule a_update as on update to a do instead (
| insert into _a (value) values (new.value);
| insert into deleted (tid) values (old.id)
);

insert into a (value) values (1);

update a set value = 2;

select * from _a;

select * from deleted;

rollback;


begin;

create table _a (id serial primary key, value int not null);

create table deleted (id serial primary key, tid int not null);

create view a as select a.id, a.value from _a a left join deleted s on s.tid = a.id where s.id is null;

create rule a_insert as on insert to a do instead insert into _a (value) values (new.value);

create rule a_update as on update to a do instead (
| insert into deleted (tid) values (old.id);
| insert into _a (value) values (new.value)
);

insert into a (value) values (1);

update a set value = 2;

select * from _a;

select * from deleted;

rollback;

###############

                                                             version

----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.1 (Debian 12.1-1.pgdg90 1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18 deb9u1) 6.3.0
20170516,64-bit
 
(1 Zeile)

BEGIN
CREATE TABLE
CREATE TABLE
CREATE VIEW
CREATE RULE
CREATE RULE
INSERT 0 1
UPDATE 0
 id | value
---- -------
  1 |     1
  2 |     2
(2 Zeilen)

 id | tid
---- -----
  1 |   1
  2 |   2
(2 Zeilen)

ROLLBACK
BEGIN
CREATE TABLE
CREATE TABLE
CREATE VIEW
CREATE RULE
CREATE RULE
INSERT 0 1
UPDATE 0
 id | value
---- -------
  1 |     1
(1 Zeile)

 id | tid
---- -----
  1 |   1
(1 Zeile)

ROLLBACK

###############

i expected the same result in both begin ... rollback sequences,
but i get different results.

i expect 2 rows in _a and one row in deleted, but both results are not that.

best regards, Andreas Sakowski

andreas@elbrief.de writes:
> create rule a_update as on update to a do instead (
> | insert into _a (value) values (new.value);
> | insert into deleted (tid) values (old.id)
> );
> [ vs ]
> create rule a_update as on update to a do instead (
> | insert into deleted (tid) values (old.id);
> | insert into _a (value) values (new.value)
> );

> i expected the same result in both begin ... rollback sequences,
> but i get different results.
> i expect 2 rows in _a and one row in deleted, but both results are not that.

Neither of these will do what you want, because in an ON UPDATE rule
"new" and "old" represent references to the output of the view; the only
difference between them is that the desired values are substituted into
the targetlist of "new".  So in either case, the first INSERT action
causes the output of the view to change, and then the second INSERT
action computes what to insert based on the new view output and does the
wrong thing.  See

https://www.postgresql.org/docs/current/rules-update.html

Using EXPLAIN on the view UPDATE command is another way to get some
insight.

The reason why using rules to update views is semi-deprecated is exactly
that it's really hard to get the mechanism to do "simple" things like
what you'd like to do here.  I'd suggest using a trigger instead.

If you want to work with rules anyway, the first thing that you have
to remember is that unlike a trigger, "new" and "old" are not simple
composite values describing single rows --- they are references to the
whole view output.  So an absolutely essential part of any rule is some
WHERE clauses to constrain the action to just the rows you want to modify.
There are examples in the chapter I linked to above.

            regards, tom lane