Skip site navigation (1) Skip section navigation (2)

Re: Thoughts about updateable views

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Jaime Casanova <systemguards(at)yahoo(dot)com>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: Thoughts about updateable views
Date: 2004-12-22 17:09:45
Message-ID: 4997.1103735385@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Richard HuxtonDate: 2004-12-22 17:10:36
Subject: Re: Thoughts about updateable views
Previous:From: Richard HuxtonDate: 2004-12-22 16:58:01
Subject: Re: Thoughts about updateable views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group