Thread: BUG #6049: Can't load dumped view with VALUES and ORDER BY

The following bug has been logged online:

Bug reference:      6049
Logged by:          Dylan Adams
Email address:      dadams@bybaxter.com
PostgreSQL version: 8.4.4
Operating system:   Linux (Fedora 12)
Description:        Can't load dumped view with VALUES and ORDER BY
Details:

If you create a view based on a VALUES statement with an ORDER BY clause,
the SQL produced by pg_dump can't be loaded back into the database.

$ createdb testdb
$ psql -c "CREATE VIEW test_view AS VALUES (1), (2), (3) ORDER BY 1; -- or
ORDER BY column1" testdb
CREATE VIEW
$ pg_dump -t test_view testdb > test_view.dump
$ psql -c "DROP VIEW test_view;" testdb
DROP VIEW
$ psql -f test_view.dump testdb
SET
SET
SET
SET
SET
SET
psql:test_view.dump:18: ERROR:  invalid reference to FROM-clause entry for
table "*VALUES*"
LINE 2:     VALUES (1), (2), (3) ORDER BY "*VALUES*".column1;
                                          ^
HINT:  There is an entry for table "*VALUES*", but it cannot be referenced
from this part of the query.
psql:test_view.dump:21: ERROR:  relation "public.test_view" does not exist

Here's the definition that gets dumped:

CREATE VIEW test_view AS
    VALUES (1), (2), (3) ORDER BY "*VALUES*".column1;

"Dylan Adams" <dadams@bybaxter.com> writes:
> If you create a view based on a VALUES statement with an ORDER BY clause,
> the SQL produced by pg_dump can't be loaded back into the database.

I've applied a patch for this.  Thanks for the report!

            regards, tom lane