Обсуждение: 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 >