Re: help with query

["Igor Neyman" <ineyman(at)perceptron(dot)com>]

Why not use information_schema?

 

select prk.table_name AS PARENT_TABLE, prk.constraint_name AS PK, 
         tc.table_name AS CHILD_TABLE, refc.constraint_name AS FK
from information_schema.table_constraints prk,  
         information_schema.referential_constraints refc,
         information_schema.table_constraints tc
where prk.table_catalog = refc.unique_constraint_catalog
     and prk.constraint_type = 'PRIMARY KEY'
     and prk.constraint_name = refc.unique_constraint_name
     and tc.constraint_name = refc.constraint_name
     and tc.constraint_type = 'FOREIGN KEY'
     and tc.table_catalog = refc.constraint_catalog
order by prk.table_name , tc.table_name;

 

Igor

---

From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Chris Hoover
Sent: Thursday, August 16, 2007 11:19 AM
To: pgsql-admin(at)postgresql(dot)org Admin
Subject: [ADMIN] help with query

I need a little bit of help.  I need to use sql to pull any 
tables that have the a foreign key referencing a given tables primary 
key.

So far I have come up with the listed query.  It works great 
for single column primary keys, but if a table has a multi column primary key, 
it is returning to many rows.  How can I get it to work for tables with 
multi-column primary keys as well as single column primary keys? 

Thanks,

Chris

select    a.relname as 
table_name,
    c.attname as 
column_name,
    w.typname as 
domain_name
from    pg_class a, 
    
pg_constraint b,
    pg_attribute c,
    
pg_type w 
where    a.oid = 
b.conrelid
and    c.atttypid = w.oid
and    
c.attnum = any (b.conkey)
and    a.oid = 
c.attrelid
and    b.contype = 'f'
and    
a.relkind = 'r'
and    c.attname in (    
select    z.attname
        
    from    pg_class x,
    
            pg_constraint 
y,
            
    pg_attribute z
        
    where    x.oid = 
y.conrelid
            
and    z.attnum = any (y.conkey)
    
        and    x.oid = 
z.attrelid
            
and    y.contype = 'p' 
        
    and    x.relname = 'table' ) 
;