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