Re: bug with dump sql to recreate view

On Mon, 7 Apr 2003, Laura Moloney wrote:

> The sql that created the original view was:
> CREATE VIEW INCUNRESOLVEDV1 AS
>     SELECT DISTINCT TRACKID FROM INCIDENT
>         WHERE STATUS = 'o'
>     EXCEPT
>     SELECT DISTINCT TRACKID FROM INCIDENT_DETAIL
>         WHERE LOWER(RESOLVEDYN) = 'y'
>     ;
>
> The sql that was generated from pgdump was:
> CREATE VIEW "incunresolvedv1" as
>     SELECT DISTINCT incident.trackid FROM incident
>         WHERE (incident.status = 'o'::"varchar")
>         ORDER BY incident.trackid
>     EXCEPT
>     SELECT DISTINCT incident_detail.trackid FROM incident_detail
>         WHERE (lower((incident_detail.resolvedyn)::text) = 'y'::text)
>         ORDER BY incident_detail.trackid;
>
> I found that if I removed the order by clauses, then the sql would execute
> correctly.
>
> I also tried to run the pgdump sql on our 7.2.3 server to see if it had been
> resolved in a newer release, but the same error occurred.
>
> Hope my explanation was clear enough. I can certainly get around it my
> running the original SQL, but I thought you might be interested in it if in fact
> it was a bug.

It seems to have been a bug in pg_dump on 7.1.x that dumped an invalid
query.  Current pg_dump dumps the two selects with parentheses around them
which is loadable.