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 ...
