how to find foreign key details (column, that is)

[Karsten Hilbert <Karsten.Hilbert@xxxxxxx>]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general