Re: R: Re: R: R: Re: schema inspection

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
>>
>>    
>>
>
>  
>