From: | Rod Taylor <rbt(at)zort(dot)ca> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Problems with dump /restore of views |
Date: | 2002-06-21 16:06:52 |
Message-ID: | 1024675612.82060.128.camel@fury.inquent.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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"
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2002-06-21 16:19:51 | Re: Our archive searching stinks |
Previous Message | Tom Lane | 2002-06-21 15:47:17 | Re: What is wrong with hashed index usage? |