Re: pg_views

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(at)Yahoo(dot)com #