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

Only first statement of two in update-rule is executing?

From: David Owen <dsowen(at)fugue88(dot)ws>
To: pgsql-general(at)postgresql(dot)org
Subject: Only first statement of two in update-rule is executing?
Date: 2008-01-30 02:42:31
Message-ID: Pine.LNX.4.64.0801291928010.10778@tux.l.fugue88.ws (view raw or flat)
Thread:
Lists: pgsql-general
I'm trying to create a table that maintains change-history and a modifable 
view over it showing only current data.  The delete and insert rules work 
well, but I'm having trouble getting my update rule to work (it contains 
two statements, the others contain only one each).

Here's a simple demonstration of my problem:

CREATE TABLE simple1(a varchar, b varchar, c timestamp DEFAULT now(),
     d timestamp, PRIMARY KEY(a, c));
CREATE VIEW simple2 AS SELECT a, b FROM simple1 WHERE d IS NULL;
INSERT INTO simple1(a, b) VALUES('a', '1');
CREATE RULE simple2_upd AS ON UPDATE TO simple2 DO INSTEAD
    (UPDATE simple1 SET d = now() WHERE a = NEW.a AND d IS NULL;
     INSERT INTO simple1(a, b) VALUES(NEW.a, NEW.b));

Now, if I do

     UPDATE simple2 SET b='2';

... I expect the the original row in simple1 to now have d=(some time), 
and a new row with (a, b, c, d) = ('a', '2', some time, NULL).

However, the new row isn't inserted; only the previous row is changed.

What am I missing?  Any pointers?  I've read through the docs (I'm on 
8.2.6) for rules several times, and nothing seems to explain this.

Thanks,
David Owen

Responses

pgsql-general by date

Next:From: Chander GanesanDate: 2008-01-30 02:54:22
Subject: Re: enabling autovacuum
Previous:From: Reece HartDate: 2008-01-30 02:36:02
Subject: Re: OT - pg perl DBI question

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