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