Re: Res: Finding all tables that have foreign keys referencing a table

Andreas Joseph Krogh <andreak(at)officenet(dot)no> writes:
> AFAICS this lists all tables which have a column named '?', which is not what I'm after. I'm after listing all columns referencing a certain column as a FOREIGN KEY.

Should be possible to dredge that out of pg_constraint ... about like
this:

select confrelid::regclass, af.attname as fcol,
conrelid::regclass, a.attname as col
from pg_attribute af, pg_attribute a,
(select conrelid,confrelid,conkey[i] as conkey, 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 = conkey and a.attrelid = conrelid;

Deconstructing those arrays in parallel is a bit of a pain :-(

regards, tom lane