Re: Rule problem with OLD / NEW record set (repost) - Mailing list pgsql-general

Ralph Graulich <maillist@shauny.de> writes:
> CREATE VIEW view_table1 AS SELECT * FROM table1;

> -- create a rule for update
> CREATE OR REPLACE RULE ru_view_table1_update
> AS
> ON UPDATE TO view_table1 DO INSTEAD
>    (
>    -- insert a new record with the old id, old version number incremented
>    -- by one, versionflag set to 'Y' and the new content
>    INSERT INTO table1 (id, version, vnoflag, content) VALUES (OLD.id,
> OLD.version+1, 'Y', NEW.content);
>    -- update the old version and set its versionflag to 'N' as it is no
>    -- longer the current record
>    UPDATE table1 SET vnoflag = 'N' WHERE id = OLD.id AND version = OLD.version;
>    );

> It seems like the UPDATE statement updates both the old and the new
> version.

Yes, because (loosely speaking) OLD refers to the view, and once you've
done the INSERT there is now another matching row in the view.  Try
doing the UPDATE first, then the INSERT.

            regards, tom lane