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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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