Thread: how to get the source table & field name of a view field
Hi, Could you tell me how to get view field's source table and field name? another word, how could I know the view field come from? Regards, Ho
> Could you tell me how to get view field's source table and field name? > another word, how could I know the view field come from? Within psql, use \d <viewname> to learn about the view's column names and types and the view definition. If you were thinking about querying system tables to get this information, start psql with the "-E" option to see how this \d <viewname> command is implemented. Regards, Christoph
On Thu, 7 Nov 2002, Prime Ho wrote: > Hi, > > Could you tell me how to get view field's source table and field name? > another word, how could I know the view field come from? SELECT definition from pg_views where viewname='<your view name>'; > > Regards, > Ho > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
Re: how to get the source table & field name of a view field
From
kkennedy@kenzoid.com (Ken Kennedy)
Date:
On Thu, Nov 07, 2002 at 05:12:20PM +0800, Prime Ho wrote: > Hi, > > Could you tell me how to get view field's source table and field name? > another word, how could I know the view field come from? > \d view_name should give you the view definition in pqsl. -- Ken Kennedy | http://www.kenzoid.com | kenzoid@io.com
> > if I want to write a function for getting the view.field's source > table.field > how could I achieve it? > This sounds like a real challange. Throughout the years I've had to learn it's always a pain to retrieve system catalog information - no matter which DBMS is in use. On the other hand, you have to face the fact that view.field's sources are not necessarily directly derived from a table.field. A view.field's source may be any expression including aggregates. So, the only way I can think of achieving that is SELECT definition from pg_views where viewname='<your view name>'; and then your function has to parse the result. Good luck. Regards, Christoph