Hi all.
Back in 2008 I asked this
question: http://www.postgresql-archive.org/Finding-all-tables-that-have-foreign-keys-referencing-a-table-td2153236.html
The solution was (and still is) this:
select confrelid::regclass, af.attname as fcol, conrelid::regclass, a.attname
ascol from pg_attribute af, pg_attribute a, (select conrelid,confrelid,conkey[i]
asconkey, confkey[i] as confkey from (select conrelid,confrelid,conkey,confkey,
generate_series(1,array_upper(conkey,1)) as i from pg_constraint where contype =
'f') ss) ss2 where af.attnum = confkey and af.attrelid = confrelid and a.attnum
= conkeyand a.attrelid = conrelid AND confrelid::regclass = 'onp_user'::
regclass ANDaf.attname = 'id'
(it lists all columns in all tables referencing the onp_user.id column)
I wonder, is this now possible using information_schema only, or are there
still pieces missing in the standard holding this back?
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>