From: | Ralph Graulich <maillist(at)shauny(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Rule problem with OLD / NEW record set (repost) |
Date: | 2005-01-28 22:19:53 |
Message-ID: | Pine.LNX.4.61.0501282318170.27344@lilly.baden-online.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I posted the question below to the pgsql-sql list, but did not receive any
answer yet. Therefore I repost it on pgsql-general in hope for any
information regarding that issue. Thanks alot.
Best regards
... Ralph ...
---
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?
---
From | Date | Subject | |
---|---|---|---|
Next Message | Ed L. | 2005-01-28 22:24:36 | rowset-returning function mismatch |
Previous Message | John Sidney-Woollett | 2005-01-28 22:18:42 | Re: Moving from Sybase to Postgres - Stored Procedures |