Thread: View definition truncated in information_schema

Hi all,
    I'm trying to retrieve the SQL definition of a view from
information_schema via:
SELECT view_definition FROM information_schema.views WHERE
table_name = 'viewname';
    It appears the definition returned is truncated at a fixed number of
characters - for this view it's 7650.
Does anybody know where this problem is in information_schema, or in the
actual storage of the text definition.  If it's the former, is there a
way I can query the text definition directly?

This occurs in both server versions 8.0 and v7.4.1, BTW.

Many thanks,

Eric

On Thu, Jan 27, 2005 at 12:09:47PM -0500, Eric E wrote:

>    I'm trying to retrieve the SQL definition of a view from
> information_schema via:
> SELECT view_definition FROM information_schema.views WHERE
> table_name = 'viewname';
>    It appears the definition returned is truncated at a fixed number of
> characters - for this view it's 7650.

What client are you using?  psql, or something else?  In simple
tests I don't see this truncation with psql (8.0.0); I wonder
if your client is truncating the output.

> Does anybody know where this problem is in information_schema, or in the
> actual storage of the text definition.  If it's the former, is there a
> way I can query the text definition directly?

information_schema.views is itself a view; you can see its definition
by executing the following in psql:

\d information_schema.views

The above shows that the view_definition column gets its value from
pg_get_viewdef().  What do the following queries return?

SELECT length(pg_get_viewdef('viewname'::regclass));
SELECT pg_get_viewdef('viewname'::regclass);

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/