Re: Thoughts about updateable views

--On Montag, März 22, 2004 16:48:29 -0500 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
>> If the user specifically asked for an updatable view, then they can't
>> have one. If they didn't specifically ask, they get the usual read only
>> view.
>
> "Specifically asked" how? AFAICS the CREATE VIEW syntax doesn't make any
> distinction. (The WITH CHECK OPTION option requires the view to be
> updateable, but I imagine we'd not support that to begin with, anyway.)

Currently no SQL spec handy (i will have one soon) , i took a look onto
O'Reillys "SQL in a
Nutshell", where the WITH CHECK OPTION is defined as follows (translated
from German....):

Only data that can be read from the specific view can be updated, fields
that aren't
part of the view can't be updated. E.g. if a view is defined to display
only the monthly
salary of an employee, it wouldn't be possible to modify an employees
hourly salary.
O'Reilly says this is SQL99. There is also a CASCADED and LOCAL keyword
mentioned
which makes this check local to the specific view, or triggers the check to
all
possible underlying views.

Makes that sense or do i misunderstand important things?

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.

--

Bernd