Thread: pg_views

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.


thanks in advance,
- lex

-- 
Lex Berezhny <LBerezhny@DevIS.com>

>
>   I'm wondering if PostgreSQL actually reparses the view definition on

> each invocation or if it stores the required information in some
> accessible place.
>

The documentation says:
Whenever a query against a view (i.e. a virtual table) is made, the
rewrite system rewrites the user's query to a query that   accesses the base tables given in the view definition
instead.

>
>   My goal is to take a view name as input and output the tables and
> columns composing the view.
>

I very much doubt this is possible, unless you step deep into
the parser defined in gram.y and scan.l, which is
as you mentioned beyond your scope.

Regars, Christoph

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 #

Jan Wieck <JanWieck@Yahoo.com> writes:
> Lex Berezhny wrote:
>> 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.

Yes.  This problem is not simple.

> 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.

As of 7.3, at least some of what Lex wants could probably be extracted
from the pg_depend entries for the view's select rule.  For example,
given

regression=# create view vv as select unique1, hundred from tenk1;

I see these entries in pg_depend:

regression=# select * from pg_depend where objid=578707;classid | objid  | objsubid | refclassid | refobjid |
refobjsubid| deptype
 
---------+--------+----------+------------+----------+-------------+---------  16410 | 578707 |        0 |       1259 |
 578705 |           0 | i  16410 | 578707 |        0 |       1259 |   578705 |           0 | n  16410 | 578707 |
0|       1259 |   443421 |           1 | n  16410 | 578707 |        0 |       1259 |   443421 |           7 | n
 
(4 rows)

The first two just link back to the owning view (hm, why are we making
two entries for that?) but the other two show that the view depends on
columns 1 and 7 of table 443421, ie, tenk1.

This won't tell you exactly how the view uses those columns, only that
they are referenced; but it might be good for something.
        regards, tom lane