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

Re: Thoughts about updateable views

From: Richard Huxton <dev(at)archonet(dot)com>
To: Jaime Casanova <systemguards(at)yahoo(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Thoughts about updateable views
Date: 2004-12-22 09:41:40
Message-ID: 41C94154.3050708@archonet.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Jaime Casanova wrote:
> In a galaxy far, far away Bernd wrote:
> 
> The context:
> http://archives.postgresql.org/pgsql-hackers/2004-03/msg00999.php
> 
> 
>>so joined views are even not updateable, too.
> 
> 
> I don't find the why of this on the specs and the 
> threads about this issue ignore the comment.

BEGIN QUOTE
In O'Reilly there are also the conditions mentioned a view has to pass 
when declaring it updateable (also SQL99):


- The SELECT of the CREATE VIEW command depends only on one table
- UNION, EXCEPT and INTERSECT are not part of the View's SELECT
- No GROUP BY or HAVING
- Pseudo-Columns are forbidden, too (but i think that can improved to 
functions....)
- No Aggregates
- No DISTINCT

so joined views are even not updateable, too.
END QUOTE

Well, I haven't checked the specs, but this list is clearly incorrect 
from a theoretical point of view (I've been thinking about this recently).

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.

Any summarising rules out updates for the summarised tables (because you 
no longer have a key available). Similarly, expression/function columns 
can't be updated (except in certain special cases).

UNION etc doesn't necessarily mean you can't update, so long as the 
underlying table/key can be identified.

For INSERTing to a view, the same rules apply, but obviously you need to 
  be able to identify table/keys for all columns in the view. This 
clearly rules out aggregates etc.

--
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-hackers by date

Next:From: Dave HartwigDate: 2004-12-22 11:33:34
Subject: Can't Restart ver 8.0b3
Previous:From: Mahmoud TaghizadehDate: 2004-12-22 09:12:36
Subject: strcoll_l an new approach for locale per column

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