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
