Re: Updating a table via a view

Thanks Tom,

> Is the base table actually a table? Or is it a view on a_hist?

It is a view.

> (Given that you say "DO INSTEAD", I'm suspicious it's a view.)
> If it's a view, then the likely problem is that after the first
> UPDATE, the view row that the notional UPDATE is for doesn't exist
> anymore. Then the INSERT doesn't fire, because it's really been
> rewritten into something along the lines of
>
> INSERT INTO a_hist (...)
> SELECT new.x, now(), 'infinity'::timestamptz, new.y, new.z
> FROM view WHERE view-conditions AND original-update's-conditions
>
> If I'm guessing correctly that the view-conditions include something
> about "a.tend < infinity", then this will produce no rows to insert.

Very good guess :-) Thats very close. Given the above logic, I am not
sure I can do what I want with a view. I have previously done this sort
of things with triggers on a table but this technique was suggested by a
colleage so I thought I'd give it a try. Now that I am aware of the way
the rule is constructing the query, I might try to restructure it a bit.

--

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Glen and Rosanne Eustace,
GodZone Internet Services, a division of AGRE Enterprises Ltd.,
P.O. Box 8020, Palmerston North, New Zealand 5301
Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015