Обсуждение: Preserving view source code
Hello, when I create a view, e.g.: CREATE VIEW my_view AS SELECT col1, col2, col3 FROM mytable; And I later retrieve the view's source using "SELECT definition FROM pg_view", the source I supplied has been altered by Postgres. The formatting has been removed completely and PG actually re-wrote the query. For the above example I would get: SELECT mytable.col1, mytable.col2, mytable.col3 FROM mytable (all in one line) Is there a way to tell PG _not_ to alter my SQL, so I can retrieve the same (or at least a very similar) version of the original statement? For procedures this is already the case, so I wonder why the view code is altered that much. Regards Thomas
Thomas Kellerer wrote: > Hello, > > when I create a view, e.g.: > And I later retrieve the view's source using "SELECT definition FROM > pg_view", the source I supplied has been altered by Postgres. > > The formatting has been removed completely and PG actually re-wrote the > query. For the above example I would get: > Is there a way to tell PG _not_ to alter my SQL, so I can retrieve the > same (or at least a very similar) version of the original statement? > > For procedures this is already the case, so I wonder why the view code > is altered that much. The body of a function is just text, so what you put in is what you get out. The view definition is presumably in the form of a parse-tree or some such, so what you get back is a human-readable form of that rather than what you type at the keyboard. It's the same with pg_dump's interpretation of your table definitions - you don't get the formatting nor even the "shape" of your definition I'm afraid. What are you trying to do that requires the original definition? -- Richard Huxton Archonet Ltd
Richard Huxton wrote on 16.10.2007 18:59: >> The formatting has been removed completely and PG actually re-wrote >> the query. For the above example I would get: > >> Is there a way to tell PG _not_ to alter my SQL, so I can retrieve the >> same (or at least a very similar) version of the original statement? >> >> For procedures this is already the case, so I wonder why the view code >> is altered that much. > > The body of a function is just text, so what you put in is what you get > out. > > The view definition is presumably in the form of a parse-tree or some > such, so what you get back is a human-readable form of that rather than > what you type at the keyboard. > > It's the same with pg_dump's interpretation of your table definitions - > you don't get the formatting nor even the "shape" of your definition I'm > afraid. > > What are you trying to do that requires the original definition? > I'm maintaining a cross-platform SQL tool that also displays source code for views, triggers etc. It would have been nice if the tool could actually display the source the same way it looked when the user ran the CREATE VIEW statement (which is possible with most of the other DBMS I support). So I guess I just have to live with that. Thanks for the answer Regards Thomas
On 16/10/2007 18:16, Thomas Kellerer wrote: > I'm maintaining a cross-platform SQL tool that also displays source code > for views, triggers etc. It would have been nice if the tool could > actually display the source the same way it looked when the user ran the > CREATE VIEW statement (which is possible with most of the other DBMS I > support). What pgAdmin does is very similar to this, and it produces fairly well-formatted SQL, if not what was entered originally - it may be worth your while digging through the pgAdmin code. Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------