From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Yury Shvetsov <rmtech(at)marbella(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SQL text of view |
Date: | 2003-11-18 20:54:09 |
Message-ID: | 3FBA86F1.70502@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Yury Shvetsov wrote:
> Hi.
>
> Where is the SQL text of view stored in the database?
> I mean the text like "SELECT the_field FROM the_table".
> I can found the function's text in "pg_proc.proerc", but can't find the same
> for a view.
You can use the function pg_get_viewdef() in 7.3 to get the text of
the view. If you start psql with the -E switch you can see the queries
it uses to render its output.
When you create a view, an ON SELECT rewrite rule is automatically
created and its parse tree is stored in pg_rewrite. pg_get_viewdef()
is ultimately executing:
SELECT *
FROM pg_catalog.pg_rewrite
WHERE ev_class = <your view's pg_class.oid> AND
rulename = '_RETURN';
and reverse-parsing the stored parse tree for you.
HTH,
Mike Mascari
mascarm(at)mascari(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Lynn.Tilby | 2003-11-18 20:54:51 | Re: Better Unilization of Memory |
Previous Message | Rick Gigger | 2003-11-18 20:43:06 | performance problem |