From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> |
Cc: | Eric D Nielsen <nielsene(at)MIT(dot)EDU>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Updateable views... |
Date: | 2003-03-05 15:33:17 |
Message-ID: | 19388.1046878397@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> I haven't had time to look into it further, but it occurs to me that
> handling views which rely on joins would be far from trivial.
Views containing joins would not be updatable; problem solved. The set
of views the automatic-rule-generation machinery needs to handle are
those defined as updatable by the SQL spec. SQL92 says
12)A <query specification> QS is updatable if and only if the fol-
lowing conditions hold:
a) QS does not specify DISTINCT.
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.
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.
Note: updatable derived table is defined in Subclause 6.3,
"<table reference>".
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.
e) The <table expression> immediately contained in QS does not
include a <group by clause> or a <having clause>.
The reference to 6.3 appears to be pointing at this:
8) A <derived table> is an updatable derived table if and only if
the <query expression> simply contained in the <subquery> of the
<table subquery> of the <derived table> is updatable.
I haven't quite wrapped my head around what clause 12d means, but 12c is
perfectly clear that you only get one table reference.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2003-03-05 15:36:06 | Re: [PATCHES] Non-colliding auto generated names |
Previous Message | Merlin Moncure | 2003-03-05 15:18:32 | Re: XML ouput for psql |