Re: Thoughts about updateable views

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

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