Re: Thoughts about updateable views

Tom Lane wrote:
> 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 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.

Agreed, but the reason we can't maintain the illusion that it's a 
"simple" table (i.e. plain CREATE TABLE) is that it's not. I might have 
a shelf_position column that, when I update it fires a trigger to 
renumber all the positions for that shelf. That breaks the illusion too.

Perhaps a more common example. A column "updated_ts" that always gets 
set to now() regardless of supplied value. That's non-intuitive (or at 
least implicit) behaviour, but perfectly common (and reasonable, I'd argue).

Now, on the client I'll grant we've got a problem unless we re-fetch 
after each update, or have some server-driven signalling. However, 
Microsoft have some sort of solution because their resultset-style model 
of the world in VB etc encounter this sort of thing.
--  Richard Huxton  Archonet Ltd