List all columns referencing an FK

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

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 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
      AND confrelid::regclass = 'onp_user'::regclass AND af.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@xxxxxxxxxx

www.visena.com