--On Montag, März 22, 2004 16:48:29 -0500 Tom Lane <[EMAIL PROTECTED]> wrote:
Gavin Sherry <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
