Re: 7.2.1: pg_dump of UNIONed VIEWs broken

On Tue, 23 Apr 2002, Bruce Momjian wrote:

> Can someone phrase a TODO item for me?

How about:
Fix pg_get_viewdef() to output fully nested selects

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

> Ian Morgan wrote:
> > PostgreSQL 7.2.1's pg_dump prduces bad SQL for VIEW's.
> >
> > The SQL for CREATE VIEW as output by pg_dump generates an error, because the
> > appropriate nesting of SELECTs with parentheses is missing:
> > ERROR: parser: parse error at or near "SELECT"
> >
> >
> > Original SQL:
> >
> > 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
> > )
> > );
> >
> >
> > As output by pg_dump:
> >
> > 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;
> >
> > As you can see, the nesting is entirely lost.