Re: Rule problem with OLD / NEW record set (repost)

[Ralph Graulich <maillist@xxxxxxxxx>]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx