Re: Updating a table via a view

> > 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
> >          );
> >         );

I suggest you put all things in a pl/pgsql function and callt that function in
instead rule. It might be that it takes that ';' as end of the SQL command.

HTH

Bye
 Shridhar

--
We'll pivot at warp 2 and bring all tubes to bear, Mr. Sulu!