Rule problem with OLD / NEW record set
От | Ralph Graulich |
---|---|
Тема | Rule problem with OLD / NEW record set |
Дата | |
Msg-id | Pine.LNX.4.61.0501270225500.10119@lilly.baden-online.de обсуждение исходный текст |
Список | 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 currentrecord 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 ...
В списке pgsql-sql по дате отправления: