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' ) ;
