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(at)tiscali(dot)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
>>
>>
>>
>
>
>