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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
