Re: Thoughts about updateable views

Tom Lane wrote:
> Richard Huxton <dev(at)archonet(dot)com> writes:
>
>>There are two things (AFAICT) you need to be able to do to update (NOTE
>>- not insert) a view.
>> 1. Identify the underlying table(s) for the updated column(s)
>> 2. Identify (primary) key values for the table(s) being updated.
>>So - I could have a join listing users and how many email aliases they
>>have (so sum()) and still update their name, so long as the key for the
>>users table was present in the view.
>
>
> 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.

If you have a many-many relationship, (say worker<=>department) then
again you may update multiple rows in the view ("WHERE dept_id=123"),
but so what - that's what you asked to do.

I'm not saying this is always the behaviour you'd want. Imagine an
address table where you have a country-code field and a lookup table of
countries. I almost certainly DON'T want the lookup table updated via
the view, but there's no easy solution to that - it's to do with the
semantics of the join, not its syntax.

--
Richard Huxton
Archonet Ltd