From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jan Wieck <JanWieck@Yahoo.com>
Cc: Lex Berezhny <LBerezhny@DevIS.com>
Cc: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
Subject: Re: pg_views
Date: Tue, 04 Feb 2003 13:46:45 -0500
Message-ID: <15770.1044384405@sss.pgh.pa.us> (raw)
In-Reply-To: <3E3FFA03.F0EF4D64@Yahoo.com>
References: <1044326043.13139.17.camel@buddha>
<3E3FFA03.F0EF4D64@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, JanWieck@Yahoo.com, LBerezhny@DevIS.com
Subject: Re: pg_views
In-Reply-To: <15770.1044384405@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox