Re: pg_views

Jan Wieck <JanWieck(at)Yahoo(dot)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