Postgres Pro
Downloads
Home   >   mailing lists

Thread: List all columns referencing an FK

List all columns referencing an FK

From
Andreas Joseph Krogh
Date:
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
Attachment

Re: List all columns referencing an FK

From
Peter Eisentraut
Date:
On 2/8/18 05:31, Andreas Joseph Krogh wrote:
> Back in 2008 I asked this
>
question: http://www.postgresql-archive.org/Finding-all-tables-that-have-foreign-keys-referencing-a-table-td2153236.html

> I wonder, is this now possible using information_schema only, or are
> there still pieces missing in the standard holding this back?

I think you'll still have the same problems if the same constraint name
appears more than once per schema.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services