Re: 7.2.1: pg_dump of UNIONed VIEWs broken

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 *
-------------------------------------------------------------------