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