Re: Updating a table via a view

On Mon, 10 Feb 2003, Glen Eustace wrote:

glen-

there is a package in the contrib directory :

postgresql-7.2.2/contrib/spi/timetravel.*

or something similar, which does this autoamtically for you. it is very
slick.

-a

> I am trying to maintain a history of rows. The base table has a start
> and end date. I am updating the table using a view and a rule. The
> rule, updates the end date on the current record and then inserts a new
> row with the modified columns, or at least thats what I want to happen.
>
> The update is occuring but the insert doesn't. I get no error but no
> row. The rule looks like this;
>
> CREATE RULE a_update
> AS ON UPDATE TO a DO INSTEAD
> (UPDATE a_hist
> SET tend = now()
> WHERE (a.x = old.x)
> AND (a.tend = 'infinity'::timestamptz);
> INSERT INTO a_hist (
> x,
> tstart,
> tend,
> y,
> z
> ) VALUES (
> new.x,
> now(),
> 'infinity'::timestamptz,
> new.y,
> new.z
> );
> );
>
> Any pointer as to what I am doing wrong ?
>
>

--

====================================
| Ara Howard
| NOAA Forecast Systems Laboratory
| Information and Technology Services
| Data Systems Group
| R/FST 325 Broadway
| Boulder, CO 80305-3328
| Email: ahoward(at)fsl(dot)noaa(dot)gov
| Phone: 303-497-7238
| Fax: 303-497-7259
====================================