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

Re: Updateable views...

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 (view raw or flat)
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

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2003-03-05 15:36:06
Subject: Re: [PATCHES] Non-colliding auto generated names
Previous:From: Merlin MoncureDate: 2003-03-05 15:18:32
Subject: Re: XML ouput for psql

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