Skip site navigation (1) Skip section navigation (2)

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

From: Ralph Graulich <maillist(at)shauny(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Rule problem with OLD / NEW record set (repost)
Date: 2005-01-30 17:11:50
Message-ID: Pine.LNX.4.61.0501301807510.25734@lilly.baden-online.de (view raw or flat)
Thread:
Lists: pgsql-general
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 ...

In response to

pgsql-general by date

Next:From: Mike-Olumide JohnsonDate: 2005-01-30 17:48:51
Subject: Freeradius and Postgresql
Previous:From: Lonni J FriedmanDate: 2005-01-30 17:03:48
Subject: Re: Pg database, need a solution to a problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group