Re: CREATE RULE on VIEW with INSERT after UPDATE does not work

"Peter Marius" <Peter(dot)Marius(at)gmx(dot)de> writes:
> I thought, the example with "mylog" would be better to
> demonstrate the problem, but it's missing the point.
> Below, if have added the code with my real problem.

> CREATE RULE sru AS ON UPDATE TO myview DO INSTEAD
> (
> UPDATE mytable SET stop = now() WHERE id = old.id AND stop IS null;
> INSERT INTO myview (id, proc, start, stop) VALUES (old.id, old.proc, now(), null);
> );

AFAICS, all you need to do is swap the ordering of those two operations.

It might help to understand that what you write as an INSERT/VALUES is
really more like INSERT ... SELECT ... FROM myview WHERE ..., the WHERE
condition being the same as was given in the "UPDATE myview" command
that the rule rewrites. As soon as you change the stop value in the
UPDATE mytable, the SELECT from the view will find nothing.

regards, tom lane