From: Jan Wieck <JanWieck@Yahoo.com>
To: Lex Berezhny <LBerezhny@DevIS.com>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: pg_views
Date: Tue, 04 Feb 2003 12:36:03 -0500
Message-ID: <3E3FFA03.F0EF4D64@Yahoo.com> (raw)
References: <1044326043.13139.17.camel@buddha>
Lex Berezhny wrote:
>
> hi,
>
> I'm trying to write some code that can analyze the database structure
> and i need a way to discover the composition of a view (the tables and
> table.column info).
>
> I've managed to do much of this by querying the pg_views for the
> definition and literally parsing the SQL myself, but obviously that has
> many limitations unless I impliment a complete SQL parser and that's
> beyond the scope of what I want :-)
>
> I'm wondering if PostgreSQL actually reparses the view definition on
> each invocation or if it stores the required information in some
> accessible place.
>
> My goal is to take a view name as input and output the tables and
> columns composing the view.
Don't forget that a view's columns can contain complex expressions
instead of simple table.column references.
The place where the real information about views is stored is
pg_rewrite. pg_views is a view itself, using a function that parses back
the rewrite rule into a human readable format. There are people around
who can read the querytree format stored in pg_rewrite as well.
So parsing pg_views output would be ... er ... parsing a query string
that was reconstructed from a parsetree that resulted from parsing a
query string ... that doesn't sound like the right thing to do.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: JanWieck@Yahoo.com, LBerezhny@DevIS.com
Subject: Re: pg_views
In-Reply-To: <3E3FFA03.F0EF4D64@Yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox