X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 867049FA2EB for ; Fri, 12 May 2006 18:26:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 46731-02 for ; Fri, 12 May 2006 18:26:19 -0300 (ADT) X-Greylist: delayed 00:29:08.511905 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id EA6D09FA293 for ; Fri, 12 May 2006 18:26:19 -0300 (ADT) Received: from perseverance.encs.concordia.ca (perseverance-96.encs.concordia.ca [132.205.96.94]) by svr4.postgresql.org (Postfix) with ESMTP id 7B1575B59A8 for ; Fri, 12 May 2006 20:57:09 +0000 (GMT) Received: from [132.205.47.225] (emilu@cacao.encs.concordia.ca [132.205.47.225]) by perseverance.encs.concordia.ca (8.13.6/8.13.6) with ESMTP id k4CKv0LY031574; Fri, 12 May 2006 16:57:00 -0400 Message-ID: <4464F69C.50505@encs.concordia.ca> Date: Fri, 12 May 2006 16:57:00 -0400 From: Emi Lu Reply-To: emilu@encs.concordia.ca User-Agent: Mozilla Thunderbird 1.0.7 (X11/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Achilleus Mantzios Cc: "rcolmegna@tiscali.it" , pgsql-sql@postgresql.org Subject: Re: R: Re: R: R: Re: schema inspection References: In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Scanned-By: MIMEDefang 2.43 on perseverance.encs.concordia.ca at 2006/05/12 16:57:00 EDT X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/104 X-Sequence-Number: 25135 If it is for multiple columns' foreign key constraint. Try this query: SELECT DISTINCT n.nspname AS from_schema_name, c.relname AS from_table_name, toSchemaName.nspname AS to_schema_name, toTable.relname as to_table_name, fk_col.attname FROM pg_catalog.pg_class AS c LEFT JOIN pg_namespace AS n ON (n.oid = c.relnamespace) INNER JOIN pg_catalog.pg_constraint AS rel ON (c.oid=rel.conrelid) LEFT JOIN pg_catalog.pg_class AS toTable ON (toTable.oid = rel.confrelid) LEFT JOIN pg_namespace AS toSchemaName ON (toSchemaName.oid = toTable.relnamespace) LEFT JOIN pg_catalog.pg_attribute AS fk_col ON fk_col.attrelid = rel.conrelid AND (position(fk_col.attnum in array_to_string(conkey, ' ')) <>0 ) WHERE rel.contype='f' ORDER BY from_schema_name, from_table_name; Ying >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 >> >> >> > > >