From: "Ken Winter" <ken@sunward.org>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: "'PostgreSQL pg-sql list'" <pgsql-sql@postgresql.org>
Subject: Re: Rule causes baffling error
Date: Mon, 19 Dec 2005 10:44:30 -0500
Message-ID: <009c01c604b3$1b007630$6603a8c0@kenxp>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <43A67879.3050003@archonet.com>

Richard ~

Thanks for your response.

Can a trigger be written on a *view*?  I can't find anything in the
PostgreSQL docs that answers this question.

I originally wrote these actions (described in my original message) as a
trigger on my base table, but then realized I was getting in deeper and
deeper trouble because (a) I was getting into cascading triggers that I
didn't want and (b) I need to enable some queries to access the base table
without triggering these actions.  That's why I set up the view, and then I
assumed that the only way I could implement these actions was as rules.  

~ Ken


> -----Original Message-----
> From: Richard Huxton [mailto:dev@archonet.com]
> Sent: Monday, December 19, 2005 4:08 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-sql list'
> Subject: Re: [SQL] Rule causes baffling error
> 
> Ken Winter wrote:
> > This rule is supposed to (1) cause an update directed to the view
> > "my_data_now" to be made to the underlying table "my_data", (2) reset
> the
> > "effective_date_and_time" of that row to 'now', (3) insert a record
> > containing the old values into "my_data", and (4) expire that "old"
> record
> > by setting its "expiration_date_and_time" to 'now'.
> 
> I think you want a trigger rather than a rule.
> 
> Rules rewrite the query structure, triggers let you deal with values on
> a row-by-row basis (for row-level triggers).
> 
> --
>    Richard Huxton
>    Archonet Ltd