Обсуждение: 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' ) ;
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' ) ;
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;
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@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Chris Hoover
Sent: Thursday, August 16, 2007 11:19 AM
To: pgsql-admin@postgresql.org Admin
Subject: [ADMIN] help with query
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' ) ;