pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_views
4+ messages / 4 participants
[nested] [flat]
* pg_views
@ 2003-02-04 02:34 Lex Berezhny <LBerezhny@DevIS.com>
0 siblings, 1 reply; 4+ messages in thread
From: Lex Berezhny @ 2003-02-04 02:34 UTC (permalink / raw)
To: pgsql-sql
hi,
I'm trying to write some code that can analyze the database structure
and i need a way to discover the composition of a view (the tables and
table.column info).
I've managed to do much of this by querying the pg_views for the
definition and literally parsing the SQL myself, but obviously that has
many limitations unless I impliment a complete SQL parser and that's
beyond the scope of what I want :-)
I'm wondering if PostgreSQL actually reparses the view definition on
each invocation or if it stores the required information in some
accessible place.
My goal is to take a view name as input and output the tables and
columns composing the view.
thanks in advance,
- lex
--
Lex Berezhny <LBerezhny@DevIS.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_views
@ 2003-02-04 09:02 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 4+ messages in thread
From: Christoph Haller @ 2003-02-04 09:02 UTC (permalink / raw)
To: pgsql-sql; +Cc: LBerezhny@DevIS.com
>
> I'm wondering if PostgreSQL actually reparses the view definition on
> each invocation or if it stores the required information in some
> accessible place.
>
The documentation says:
Whenever a query against a view (i.e. a virtual table) is made, the
rewrite system rewrites the user's query to a query that
accesses the base tables given in the view definition instead.
>
> My goal is to take a view name as input and output the tables and
> columns composing the view.
>
I very much doubt this is possible, unless you step deep into
the parser defined in gram.y and scan.l, which is
as you mentioned beyond your scope.
Regars, Christoph
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_views
@ 2003-02-04 17:36 Jan Wieck <JanWieck@Yahoo.com>
parent: Lex Berezhny <LBerezhny@DevIS.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Wieck @ 2003-02-04 17:36 UTC (permalink / raw)
To: Lex Berezhny <LBerezhny@DevIS.com>; +Cc: pgsql-sql
Lex Berezhny wrote:
>
> hi,
>
> I'm trying to write some code that can analyze the database structure
> and i need a way to discover the composition of a view (the tables and
> table.column info).
>
> I've managed to do much of this by querying the pg_views for the
> definition and literally parsing the SQL myself, but obviously that has
> many limitations unless I impliment a complete SQL parser and that's
> beyond the scope of what I want :-)
>
> I'm wondering if PostgreSQL actually reparses the view definition on
> each invocation or if it stores the required information in some
> accessible place.
>
> 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.
The place where the real information about views is stored is
pg_rewrite. pg_views is a view itself, using a function that parses back
the rewrite rule into a human readable format. There are people around
who can read the querytree format stored in pg_rewrite as well.
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.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_views
@ 2003-02-04 18:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-02-04 18:46 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Lex Berezhny <LBerezhny@DevIS.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-02-04 18:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-04 02:34 pg_views Lex Berezhny <LBerezhny@DevIS.com>
2003-02-04 17:36 ` Jan Wieck <JanWieck@Yahoo.com>
2003-02-04 18:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-04 09:02 Re: pg_views Christoph Haller <ch@rodos.fzk.de>
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