Re: Updateable views...

After finding the SQL92 draft spec that Tom quoted from earlier I think I
understand the conditions for the spec's version of view updatability. I've
made few comments below on the conditions and I'ld appreciate it if anyone
would correct any mis-interpretations on my part.

> 12)A <query specification> QS is updatable if and only if the fol-
> lowing conditions hold:
>
> a) QS does not specify DISTINCT.
No explanation needed.

>
> b) Every <value expression> contained in the <select list> imme-
> diately contained in QS consists of a <column reference>, and
> no <column reference> appears more than once.
This appears to say that the select list must be of the form:
[<qualifier> <period>] <column name> [[AS] <column name>] [, ...]

No operations/functions may be applied to the column. Columns may be
renamed from the base table to the view using either "SELECT .. AS .." in the
query defining the view or in the "column name list" of the view, the
latter taking precedence if specified.

No column in the view may be a literal constant. No column from the base
table may appear more than once. (The '*' is expanded as discussed in the
spec into a form that matches the format listed above.)

> c) The <from clause> immediately contained in the <table ex-
> pression> immediately contained in QS specifies exactly one
> <table reference> and that <table reference> refers either to
> a base table or to an updatable derived table.
No joins (implicit or explicit) are allowed in an updateable view.
Updateable derived tables include: views that meet the requirements as
well as unnamed, intermediate dervived tables that meet the same standards.

I beleive this should allow queries such as:
UPDATE (SELECT bar, baz FROM foo) SET bar=1 WHERE baz=2;
as well as the
CREATE VIEW foo_view AS SELECT bar, baz FROM foo;
UPDATE foo_view SET bar=1 WHERE baz==2;
DROP VIEW foo_view;
three-query analog.

However the one-query version can't be handled by the auto-
generated ON UPDATE/DELETE/INSERT rules for views that I'm looking at.

CREATE VIEW foo_view AS SELECT bar, baz FROM (SELECT bar, baz FROM foo) AS qux;
should yield an updateable view as the derived table used in the from clause
is itself an updateable derived table.

> d) If the <table expression> immediately contained in QS imme-
> diately contains a <where clause> WC, then no leaf generally
> underlying table of QS shall be a generally underlying table
> of any <query expression> contained in WC.
I beleive this is saying that the ultimate base tables of the QS and the
ultimate base table invoved in a query in the WC must be disjoint.
e.g. (stupid example, but...)
CREATE VIEW foo_view AS SELECT bar,baz FROM foo WHERE bar<10;
CREATE VIEW foo2_view AS SELECT bar,baz FROM foo
WHERE baz in (SELECT bar,baz FROM foo_view) AND baz >15;

foo_view would be updateable. foo2_view would not be as the same ultimate
base table appears in both the table expression for the view and in the query
expression of the WC. Changing foo2_view to
CREATE VIEW foo2_view AS SELECT bar,baz, FROM foo_view ...
would not fix the problem as its the _ultimate_ base tables that matter.

> e) The <table expression> immediately contained in QS does not
> include a <group by clause> or a <having clause>.
No explanation needed.

Eric Nielsen