From: Christoph Haller <ch@rodos.fzk.de>
To: maillist@shauny.de
Cc: pgsql-sql@postgresql.org
Subject: Re: Rule problem with OLD / NEW record set
Date: Mon, 07 Feb 2005 11:23:07 +0100
Message-ID: <4207418B.E7807E82@rodos.fzk.de> (raw)
References: <Pine.LNX.4.61.0501270225500.10119@lilly.baden-online.de>
Ralph Graulich wrote:
>
> Hello everyone,
>
> given is a table with a version history kind of thing I am currently
> working on. Upon this table there is a view and the application interacts
> with the view only, updating/inserting/deleting is controlled by rules. It
> seems like the record set "OLD" gets changed when it is used in a SQL
> expression:
>
> CREATE TABLE table1
> (
> id INTEGER NOT NULL,
> version INTEGER NOT NULL DEFAULT 0,
> vnoflag CHAR(1),
> content VARCHAR(20)
> );
>
> INSERT INTO table1 (id, version, vnoflag, content) VALUES (1, 1, 'Y',
> 'Test');
>
> 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;
> );
>
> SELECT * FROM view_table1;
> id | version | vnoflag | content
> ----+---------+---------+---------
> 1 | 1 | Y | Test
> (1 row)
>
> UPDATE view_table1 SET content = 'New Test' WHERE id = 1 AND vnoflag =
> 'Y';
> SELECT * FROM view_table1;
> id | version | vnoflag | content
> ----+---------+---------+----------
> 1 | 1 | N | Test
> 1 | 2 | N | New Test
>
> It seems like the UPDATE statement updates both the old and the new
> version. If I correctly go through the statements by hand, they should
> read:
>
> INSERT INTO table1 (id, version, vnoflag, content) VALUES (1, 1+1, 'Y',
> 'New Test');
> UPDATE table1 SET vnoflag = 'N' WHERE id = 1 AND version = 1;
>
> If I change the UPDATE statement to read:
>
> UPDATE table1 SET vnoflag = 'N' WHERE id = OLD.id AND vno = NEW.vno-1;
>
> it works like expected:
>
> id | version | vnoflag | content
> ----+---------+---------+----------
> 1 | 2 | Y | New Test
> 1 | 1 | N | Test
>
> Where is my logical error? Shouldn't the first UPDATE statement suffice?
>
> Best regards
> ... Ralph ...
>
I've read your mail pretty late (meaning today), and I was surprised
about what is happening the same you were.
But after reading the manual
$PGSQLD/doc/html/rules-update.html Chapter 34. The Rule System
and especially 34.3.1.1. A First Rule Step by Step
it became obvious what's going on behind a rule execution.
HTH
Regards, Christoph
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, maillist@shauny.de
Subject: Re: Rule problem with OLD / NEW record set
In-Reply-To: <4207418B.E7807E82@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox