Re: Dump/restore of views containing select distinct fails

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> => create view testview as select relname, 'Constant'::text from pg_class;

I assume you meant SELECT DISTINCT there...

> => \d testview
> View "testview"
> Attribute | Type | Modifier
> -----------+------+----------
> relname | name |
> ?column? | text |
> View definition: SELECT DISTINCT pg_class.relname, 'Constant'::text FROM
> pg_class ORDER BY pg_class.relname, 'Constant'::text;

> Note how the order by clause is not valid SQL. You get

> ERROR: Non-integer constant in ORDER BY

Ooops.

> I suppose the ORDER BY clause appears because of some weird query parse
> tree hackery and is not easy to get rid of.

Not without parsetree changes to distinguish explicit from implicit
sortlist items (yet another place where we shot ourselves in the foot
by not keeping adequate info about the original form of a query...)

> Maybe using column numbers
> instead of spelling out the select list again would work?

Yes, I think that's what we need to do. This particular case could
perhaps be handled by allowing non-integer constants to fall through
in findTargetlistEntry(), but that solution still fails for

regression=# create view vv1 as select distinct f1, 42 from int4_tbl;
CREATE
regression=# \d vv1
View "vv1"
Attribute | Type | Modifier
-----------+---------+----------
f1 | integer |
?column? | integer |
View definition: SELECT DISTINCT int4_tbl.f1, 42 FROM int4_tbl ORDER BY int4_tbl.f1, 42;

Basically we should not let the rule decompiler emit any simple constant
literally in ORDER BY --- it should emit the column number instead,
turning this into

SELECT DISTINCT int4_tbl.f1, 42 FROM int4_tbl ORDER BY int4_tbl.f1, 2;

(I think we should do this only for literal constants, keeping the
more-symbolic form whenever possible.) Will work on it.

regards, tom lane