Re: Thoughts about updateable views

Tom Lane wrote:
> Richard Huxton <dev@archonet.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