For some reason a view with a select distinct, an order and an exception
by will cause pg_dump to output a double order by -- one for each select
which of course is bad SQL.


PSQL
====
rbt_t=# create view test as select distinct relname, reltuples, relnatts
from pg_class where relkind = 't' except select relname, reltuples,
relnatts from pg_class where relkind = 't' and relnatts > 4 order by
relname;
CREATE

rbt_t=# select * from test;
    relname     | reltuples | relnatts 
----------------+-----------+----------
 pg_toast_1255  |         0 |        3
 pg_toast_16384 |         0 |        3
 pg_toast_16386 |         0 |        3
 pg_toast_16408 |         0 |        3
 pg_toast_16410 |         5 |        3
 pg_toast_16416 |         0 |        3



PG_DUMP
=======
--
-- TOC Entry ID 2 (OID 337283)
--
-- Name: test Type: VIEW Owner: rbt
--

CREATE VIEW "test" as SELECT DISTINCT pg_class.relname,
pg_class.reltuples, pg_class.relnatts FROM pg_class WHERE
(pg_class.relkind = 't'::"char") ORDER BY pg_class.relname,
pg_class.reltuples, pg_class.relnatts EXCEPT SELECT pg_class.relname,
pg_class.reltuples, pg_class.relnatts FROM pg_class WHERE
((pg_class.relkind = 't'::"char") AND (pg_class.relnatts > 4)) ORDER BY
1;


PSQL
====
rbt_t=# drop view test;
DROP
rbt_t=# CREATE VIEW "test" as SELECT DISTINCT pg_class.relname,
pg_class.reltuples, pg_class.relnatts FROM pg_class WHERE
(pg_class.relkind = 't'::"char") ORDER BY pg_class.relname,
pg_class.reltuples, pg_class.relnatts EXCEPT SELECT pg_class.relname,
pg_class.reltuples, pg_class.relnatts FROM pg_class WHERE
((pg_class.relkind = 't'::"char") AND (pg_class.relnatts > 4)) ORDER BY
1;
ERROR:  parser: parse error at or near "EXCEPT"


21 Jun 2002 12:06:52 -0400
Rod Taylor <rbt@zort.ca>
Problems with dump /restore of views
Rod Taylor wrote:
> 
> For some reason a view with a select distinct, an order and an exception
> by will cause pg_dump to output a double order by -- one for each select
> which of course is bad SQL.

I think views should not have ORDER BY clauses at all in the first
place. 


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Fri, 21 Jun 2002 12:45:20 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Problems with dump /restore of views
Rod Taylor <rbt@zort.ca> writes:
> For some reason a view with a select distinct, an order and an exception
> by will cause pg_dump to output a double order by -- one for each select
> which of course is bad SQL.

This is fixed in current sources and REL7_2 branch.

			regards, tom lane

Fri, 21 Jun 2002 13:13:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Problems with dump /restore of views 