BUG #13789: pg_admin produces table definitiona instead of a view

amsl.sm@gmail.com writes:

I have a view defined with the following sql statement:

CREATE OR REPLACE VIEW my_view AS
(
select s.id as start_id
from start s
group by s.id
order by start_date desc
);

Note that this view definition isn't even legal unless start.id is
a primary key, otherwise you get

ERROR: column "s.start_date" must appear in the GROUP BY clause or be used in an aggregate function

When pg_admin exports this view it outputs it as as table not view:

CREATE TABLE my_view (
start_id integer
);

This is not a bug; if you look further down you'll find something like

CREATE RULE "_RETURN" AS
ON SELECT TO my_view DO INSTEAD SELECT s.id AS start_id
FROM start s
GROUP BY s.id
ORDER BY s.start_date DESC;

which converts the table to a view (admittedly in a not-very-obvious way).
Because of the dependency on start's primary key, the view can't simply
be defined up at the top of the dump. This is how pg_dump chooses to
break the circularity.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Tom,

Thanks for reply. I only noticed this during DB restore process where I was
getting an error on the line "CREATE TABLE my_view ...". From what I see,
this view was never restored.

thanks,
Alex

On Wed, Dec 2, 2015 at 9:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: