Hello all,
suppose I know that there are several tables with foreign
keys pointing to
demographics.identity.pk
With the help of pg_constraint I can get a list of *tables*
which hold those foreign keys (schema = demographics, tbl = identity,
col = pk):
select
%(schema)s as referenced_schema,
%(tbl)s as referenced_table,
%(col)s as referenced_column,
conrelid::regclass as foreign_table,
confkey as referenced_column_list
from
pg_constraint
where
contype = 'f'
and
confrelid = (
select oid from pg_class where relname = %(tbl)s and relnamespace = (
select oid from pg_namespace where nspname = %(schema)s
)
) and
(
select attnum
from pg_attribute
where
attrelid = (select oid from pg_class where relname = %(tbl)s and relnamespace = (
select oid from pg_namespace where nspname = %(schema)s
))
and
attname = %(col)s
) = any(confkey)
I cannot, however, for the life of it find out how to learn
the *column* the foreign key is on. IOW I can find out that
clinical.allergy
has a foreign key to
demographics.identity.pk
but I cannot find out that the column representing the
foreign key is
clinical.allergy.fk_identity
How can I go about this ?
Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
