By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

SQL text of view

P: n/a
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.

Yury Shvetsov.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2

P: n/a
Yury Shvetsov schrieb:
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.

Yury Shvetsov.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

What about

SELECT definition
FROM pg_views
WHERE viewname = <name of view>

Thomas

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.