Re: Thoughts about updateable views

Richard Huxton <dev@archonet.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 likeUPDATE 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