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. # #================================================== [EMAIL PROTECTED] # ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
