From: Emi Lu <emilu@encs.concordia.ca>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: rcolmegna@tiscali.it <rcolmegna@tiscali.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: R: Re: R: R: Re: schema inspection
Date: Fri, 12 May 2006 16:57:00 -0400
Message-ID: <4464F69C.50505@encs.concordia.ca> (raw)
In-Reply-To: <Pine.LNX.4.44.0603171208490.27392-100000@matrix.gatewaynet.com>
References: <Pine.LNX.4.44.0603171208490.27392-100000@matrix.gatewaynet.com>
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.tiscal...
>>
>>
>>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: emilu@encs.concordia.ca, achill@matrix.gatewaynet.com, rcolmegna@tiscali.it
Subject: Re: R: Re: R: R: Re: schema inspection
In-Reply-To: <4464F69C.50505@encs.concordia.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox