Re: Res: Finding all tables that have foreign keys referencing a table - Mailing list pgsql-sql

From Tom Lane
Subject Re: Res: Finding all tables that have foreign keys referencing a table
Date
Msg-id 22895.1226088573@sss.pgh.pa.us
Whole thread Raw
In response to Re: Res: Finding all tables that have foreign keys referencing a table  (Andreas Joseph Krogh <andreak@officenet.no>)
Responses Re: Res: Finding all tables that have foreign keys referencing a table
List pgsql-sql
Andreas Joseph Krogh <andreak@officenet.no> writes:
> AFAICS this lists all tables which have a column named '?', which is not what I'm after. I'm after listing all
columnsreferencing 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
(selectconrelid,confrelid,conkey,confkey,               generate_series(1,array_upper(conkey,1)) as i        from
pg_constraintwhere 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


pgsql-sql by date:

Previous
From: Andreas Joseph Krogh
Date:
Subject: Re: Res: Finding all tables that have foreign keys referencing a table
Next
From: Andreas Joseph Krogh
Date:
Subject: Re: Res: Finding all tables that have foreign keys referencing a table