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 [EMAIL PROTECTED] έγραψε στις 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
