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!