X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DCF62474E44 for ; Tue, 4 Feb 2003 13:47:06 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id E381F474E42 for ; Tue, 4 Feb 2003 13:47:04 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h14Ikk5u015771; Tue, 4 Feb 2003 13:46:46 -0500 (EST) To: Jan Wieck Cc: Lex Berezhny , "'pgsql-sql@postgresql.org'" Subject: Re: pg_views In-reply-to: <3E3FFA03.F0EF4D64@Yahoo.com> References: <1044326043.13139.17.camel@buddha> <3E3FFA03.F0EF4D64@Yahoo.com> Comments: In-reply-to Jan Wieck message dated "Tue, 04 Feb 2003 12:36:03 -0500" Date: Tue, 04 Feb 2003 13:46:45 -0500 Message-ID: <15770.1044384405@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/81 X-Sequence-Number: 11479 Jan Wieck 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