Re: create rule with multiple commands

andreas(at)elbrief(dot)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