pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRule problem with OLD / NEW record set
2+ messages / 2 participants
[nested] [flat]
* Rule problem with OLD / NEW record set
@ 2005-01-27 01:42 Ralph Graulich <maillist@shauny.de>
2005-02-07 10:23 ` Re: Rule problem with OLD / NEW record set Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 2+ messages in thread
From: Ralph Graulich @ 2005-01-27 01:42 UTC (permalink / raw)
To: pgsql-sql
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 ...
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Rule problem with OLD / NEW record set
2005-01-27 01:42 Rule problem with OLD / NEW record set Ralph Graulich <maillist@shauny.de>
@ 2005-02-07 10:23 ` Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 2+ messages in thread
From: Christoph Haller @ 2005-02-07 10:23 UTC (permalink / raw)
To: maillist@shauny.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-02-07 10:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-27 01:42 Rule problem with OLD / NEW record set Ralph Graulich <maillist@shauny.de>
2005-02-07 10:23 ` Christoph Haller <ch@rodos.fzk.de>
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