[SQL] Related tables to a view
Hi I need a function which are able to return the underlying tables to a view. If I have a view which is a selection from field1 and field2 from table1 and field1 and field2 from table2. How can I make a selection which returns the relation for the fields in a way so I am able to se which field belongs to which table. SELECT * FROM pg_views WHERE viewname='TestView' will return the schema and the definition for me. SELECT * FROM pg_class WHERE relname = 'TestView' will return some oids etc. SELECT * FROM pg_attribute JOIN pg_class ON attrelid = pg_class.oid AND pg_class.relname='TestView' will return the fields including oids etc. But I just cant find out how to find the tables for each field. I hope someone are able to help me with this problem. Regards Jan Andersen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Related tables to a view
On Mon, Dec 25, 2006 at 10:52:03PM +0100, Jan Meyland Andersen wrote: > If I have a view which is a selection from field1 and field2 from table1 > and field1 and field2 from table2. > > How can I make a selection which returns the relation for the fields in a > way so I am able to se which field belongs to which table. You could query pg_depend to find out which tables and columns the view's rewrite rule depends on but that's not as specific as what you're requesting. I'm not aware of a way to associate a particular table column with a particular view column short of parsing the view definition or rule action; doing so would have to allow for the possibility of a view column deriving its value from an arbitrarily complex expression involving multiple tables, subqueries, etc. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
