Re: [ADMIN] help with query

[Igor Neyman]

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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Chris Hoover
Sent: Thursday, August 16, 2007 11:19 AM
To: pgsql-admin@postgresql.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' ) ;