Re: Thoughts about updateable views

Richard Huxton <dev(at)archonet(dot)com> writes:
> Tom Lane wrote:
>> No; you'd also have to have some guarantee that a given underlying table
>> row gives rise to at most one join row. If the same table row gives
>> rise to multiple join rows, then a request specifying an UPDATE of just
>> one of those join rows can't be satisfied.

> But you can't specify an update of a single row, only those where
> certain values match. Say you have a view "user_email_vw" with the
> following columns (from obvious tables):
> user_email_vw: u_id, u_name, e_id, e_address

> Updating the view "WHERE u_id=123" may well update more than one row
> (where a user has multiple emails), but that's exactly equivalent to
> updating the user-table "WHERE u_name = 'John Smith'". In the view
> (u_id) is not a key any more.

Consider a request like
UPDATE vw SET u_name = 'Joe' WHERE u_id = 123 AND e_id = 456;
where u_id 123 links to multiple e_ids including 456. There is no way
to update the underlying tables in such a way that only this row of the
view changes. Therefore you can't sustain the illusion that the view is
an updatable table.

regards, tom lane