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@fsl.noaa.gov
 | Phone:  303-497-7238
 | Fax:    303-497-7259
 ====================================