Hi Tom,
> Yes, because (loosely speaking) OLD refers to the view, and once you've
> done the INSERT there is now another matching row in the view. Try
> doing the UPDATE first, then the INSERT.
First of all thanks alot for your input. - If I change the order of the
statement, so the UPDATE comes before the INSERT statement, I do only get
the following result:
UPDATE view_table1 SET content = 'New Test' WHERE id = 1 AND vnoflag =
'Y';
UPDATE 1
SELECT * FROM view_table1;
id | version | vnoflag | content
----+---------+---------+---------
1 | 1 | N | Test
So the update works, but no row is inserted, however the rule now looks
like:
CREATE OR REPLACE RULE ru_view_table1_update
AS
ON UPDATE TO view_table1 DO INSTEAD
(
UPDATE table1 SET vnoflag = 'N' WHERE id = OLD.id AND version =
OLD.version;
INSERT INTO table1 (id, version, vnoflag, content) VALUES (OLD.id,
OLD.version+1, 'Y', NEW.content);
);
It seems like the best solution would involve storing the old values as
well as the new values into temporary variables during the execution of
the rule? Do you know wether that affects the performance?
Best regards
... Ralph ...
