Re: 7.2.1: pg_dump of UNIONed VIEWs broken

On Mon, 22 Apr 2002, Tom Lane wrote:

> Ian Morgan <imorgan@webcon.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@webcon.net         PGP: #2DA40D07          www.webcon.net
    *  Customized Linux network solutions for your business  *
-------------------------------------------------------------------