Re: INSERT/UPDATE/DELETE Views

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Tue, May 20, 2003 at 01:01:01PM +0300, Kaarel wrote:
>> I'm relatively new to PostgreSQL. I was wondering why were
>> INSERT/UPDATE/DELETE view diabled in PostgreSQL?

> You can define rules to make insert, update and delete work on views. I'm
> surprised it was ever enabled by default since in the general case it is
> impossible to workout what the rules should be.

It never was; Kaarel misunderstands the history.

A view used to be an actual table that happened to have an ON SELECT DO
INSTEAD rule attached to it. That rule did not prevent you from doing
an INSERT ... but the inserted row went into the physical table. Where
you could not see it, because the ON SELECT rule would redirect any
attempt to select it. Likewise you could update and delete such rows,
but you were flying blind all the time.

This was obviously useless, so we got rid of the physical table
associated with views.

What should happen, but no one has got round to yet, is to automatically
generate appropriate ON INSERT, UPDATE, DELETE rules for cases where the
view structure is simple enough that we can work out exactly what those
rules ought to be. I believe that such cases correspond pretty closely
to the rules laid down in the SQL spec for whether a view is updatable.

regards, tom lane