Thread: Update through views?

I was reading in Bruce's book this morning about rules etc.  He gave an
example of how to update the underlying tables of a view using rules.  My
question ('cause the memory isn't what it used to be) is;  Will 7.1 allow
inserts, updates, and deletes through views?  I remember something about
UNIONs and friends working but not these other thingys.


TIA,
Rod
--

On 3/7/01, 7:32:26 PM, Roderick "A." Anderson <raanders@tincan.org> wrote
regarding [GENERAL] Update through views?:

> I was reading in Bruce's book this morning about rules etc.  He gave an
> example of how to update the underlying tables of a view using rules.  My
> question ('cause the memory isn't what it used to be) is;  Will 7.1 allow
> inserts, updates, and deletes through views?  I remember something about
> UNIONs and friends working but not these other thingys.

Nope - and I'm not sure how Postgres is ever going to figure out how to
update/insert on a view in the general case. The view might use UNION,
GROUP BY and CASE and effectively be read-only.

That being said, for a simple view building your own rules is simple
enough. If you're not subscribed to pgsql-sql check the mailing archives
for some recent discussion on things to be careful of (involving me, Jens
Hartwig and Tom Lane trying above and beyond the call of duty to explain
things to us)

- Richard Huxton

dev@archonet.com writes:
> Nope - and I'm not sure how Postgres is ever going to figure out how to
> update/insert on a view in the general case. The view might use UNION,
> GROUP BY and CASE and effectively be read-only.

I can guarantee that we won't ever support these things on an arbitrary
view; there'll always be a need for hand-made rules in cases where the
system is too dumb to figure out a plausible rule for updating a view,
but the programmer knows what he wants to have happen.

The SQL spec calls out a set of conditions for a view being "updatable",
which essentially means that the view is simple enough that a DBMS
should be able to derive update rules for it automatically.  At some
point we'll probably try to build machinery to implement automatic rule
creation for those sorts of views.  But it'll always be possible to
write your own rules if you don't like the automatic ones or the view
is too complex for automatic rule creation.

            regards, tom lane

On Wed, 7 Mar 2001, Tom Lane wrote:

> I can guarantee that we won't ever support these things on an arbitrary
> view; there'll always be a need for hand-made rules in cases where the
> system is too dumb to figure out a plausible rule for updating a view,
> but the programmer knows what he wants to have happen.

As Richard Huxton pointed out.  I was taking a very simplistic view of
views. :-)

>
> The SQL spec calls out a set of conditions for a view being "updatable",
> which essentially means that the view is simple enough that a DBMS
> should be able to derive update rules for it automatically.  At some
> point we'll probably try to build machinery to implement automatic rule
> creation for those sorts of views.

Sounds good but given my now enlightened view I'd probably not use it.

> But it'll always be possible to write your own rules if you don't like
> the automatic ones or the view is too complex for automatic rule
> creation.

As someone stated (I think Larry Wall) if programming were easy it would
take something as complicated as a human to do it.


Thanks for the information.


Rod
--