Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Cc: pgsql-sql(at)postgresql(dot)org, paulo matadr <saddoness(at)yahoo(dot)com(dot)br>
Subject: Re: Res: Finding all tables that have foreign keys referencing a table
Date: 2008-11-07 20:09:33
Message-ID: 22895.1226088573@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

Responses

pgsql-sql by date

Next:From: Andreas Joseph KroghDate: 2008-11-07 20:34:44
Subject: Re: Res: Finding all tables that have foreign keys referencing a table
Previous:From: Andreas Joseph KroghDate: 2008-11-07 19:46:10
Subject: Re: Res: Finding all tables that have foreign keys referencing a table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group