X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 23F5E9DCCE9 for ; Fri, 17 Mar 2006 05:15:46 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21295-10 for ; Fri, 17 Mar 2006 05:15:47 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from matrix.gatewaynet.com (host2.dynacom.ondsl.gr [62.103.35.210]) by postgresql.org (Postfix) with ESMTP id 8E2BA9DCCB8 for ; Fri, 17 Mar 2006 05:15:43 -0400 (AST) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id k2HALYOp027421; Fri, 17 Mar 2006 12:21:34 +0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id k2HALY9M027417; Fri, 17 Mar 2006 12:21:34 +0200 Date: Fri, 17 Mar 2006 12:21:34 +0200 (EET) From: Achilleus Mantzios To: "rcolmegna@tiscali.it" cc: pgsql-sql@postgresql.org Subject: Re: R: Re: R: R: Re: schema inspection In-Reply-To: <19570915.1142585814389.JavaMail.root@ps20> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-7 Content-Transfer-Encoding: 8BIT X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.112 required=5 tests=[AWL=0.112] X-Spam-Score: 0.112 X-Spam-Level: X-Archive-Number: 200603/247 X-Sequence-Number: 24593 O rcolmegna@tiscali.it Ýãñáøå óôéò Mar 17, 2006 : > > > >SELECT c1.relname,c2.relname from pg_constraint cons,pg_class c1, > pg_class > >c2 where cons.conrelid=c1.oid and cons.confrelid = c2.oid; > > > >for column(s) names you will have to do extra homework. > > Thanks! I have obtained my query! Here is: > > SELECT > (SELECT relname FROM pg_catalog.pg_class WHERE oid=conrelid) AS > fromTbl, > (SELECT relname FROM pg_catalog.pg_class WHERE oid=confrelid) AS > toTbl, > (SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid=conrelid > AND conkey[1]=attnum) AS viaCol > FROM pg_catalog.pg_constraint AS rel WHERE contype='f'; Well thats it if you use only *single column* Foreign keys. In the general case the above will need extra work. Of course you will also have to ensure that the constraint is indeed a FK constraint, that the column is not droped, etc.... which leads to the answer that enabling statement logging, and then \d and watching the log is a very good friend too. > > TIA > Roberto Colmegna > > > > > Tiscali ADSL 4 Mega Flat > Naviga senza limiti con l'unica Adsl a 4 Mega di velocità a soli 19,95 € al mese! > Attivala subito e hai GRATIS 2 MESI e l'ATTIVAZIONE. > http://abbonati.tiscali.it/banner/middlepagetracking.html?c=webmailadsl&r=http://abbonati.tiscali.it/adsl/sa/4flat_tc/&a=webmail&z=webmail&t=14 > -- -Achilleus