Re: pg_views

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