Обсуждение: BUG #13789: pg_admin produces table definitiona instead of a view

The following bug has been logged on the website:

Bug reference:      13789
Logged by:          Alex Maslennikov
Email address:      amsl.sm@gmail.com
PostgreSQL version: 9.4.5
Operating system:   Windows 7
Description:

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
);

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

CREATE TABLE my_view (
    start_id integer
);

Removing "order by" from view fixes the problem, but "order by" is a valid
syntax for 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

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:

> 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
>