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

Re: 7.2.1: pg_dump of UNIONed VIEWs broken

From: Ian Morgan <imorgan(at)webcon(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: 7.2.1: pg_dump of UNIONed VIEWs broken
Date: 2002-04-22 15:55:50
Message-ID: Pine.LNX.4.44.0204221141260.17139-100000@light.webcon.net (view raw or flat)
Thread:
Lists: pgsql-bugs
On Mon, 22 Apr 2002, Tom Lane wrote:

> Ian Morgan <imorgan(at)webcon(dot)net> writes:
> > The SQL for CREATE VIEW as output by pg_dump generates an error, because the
> > appropriate nesting of SELECTs with parentheses is missing:
>
> Hmm.  You shouldn't *need* parentheses in most cases ...
>
> > CREATE VIEW "agents" as (
> >         (
> >         SELECT DISTINCT
> >                 employees.contact_id
> >         FROM
> >                 employees
> >         ORDER BY
> >                 employees.contact_id
> >         )
> > UNION
> >         (
> >         SELECT DISTINCT
> >                 contractors.contact_id
> >         FROM
> >                 contractors
> >         ORDER BY
> >                 contractors.contact_id
> >         )
> > );
>
> although I suppose this is a counterexample.  Would it help any to point
> out that ORDER BY inside an arm of a UNION is a complete waste of
> cycles?  That's probably why it didn't occur to anyone to test this.
>
> BTW, the SELECT DISTINCTs are also a waste of cycles, since UNION will
> do that anyway.

Interesting observations. I knew the ORDER BYs were irrelevant, but hadn't
gotten around to removing them. The DISTINCTs, on the other hand were a
throwback to some old queries, and I completely missed that the UNION would
do that for me.. hehe.. oops.

Anyhow, rewriting the select:

Works:

SELECT employees.contact_id FROM employees
UNION
SELECT contractors.contact_id FROM contractors;

Does not work:

SELECT employees.contact_id FROM employees ORDER BY employees.contact_id
UNION
SELECT contractors.contact_id FROM contractors ORDER BY contractors.contact_id;

Even if adding ORDER BY to a UNION is really sub-optimal SQL, shouldn't
pg_dump (and even psql's \d <viewname>) produce SQL that actually works for
any query that was originally accepted when creating the VIEW?
Otherwise, maybe having ORDER BY in a VIEW's SELECT should be made illegal?


Regards,
Ian Morgan
-- 
-------------------------------------------------------------------
 Ian E. Morgan        Vice President & C.O.O.         Webcon, Inc.
 imorgan(at)webcon(dot)net         PGP: #2DA40D07          www.webcon.net
    *  Customized Linux network solutions for your business  *
-------------------------------------------------------------------


In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-04-22 16:41:12
Subject: Re: 7.2.1: pg_dump of UNIONed VIEWs broken
Previous:From: Tom LaneDate: 2002-04-22 15:00:49
Subject: Re: Bug #638: Buggy select statment with numeric

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