Lists: | pgsql-general |
---|
From: | Eric E <whalesuit(at)bonbon(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | View definition truncated in information_schema |
Date: | 2005-01-27 17:09:47 |
Message-ID: | 41F9205B.6030506@bonbon.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Eric E <whalesuit(at)bonbon(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: View definition truncated in information_schema |
Date: | 2005-01-27 19:28:00 |
Message-ID: | 20050127192800.GA46368@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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/