Thread: Search for restricting foreign keys

Is there a way in PG 7.3, given a field, to find out what other tables &
records are linked to it via a foreign key? EG:

create table cities (id serial primary key,
 title varchar not null);
insert into cities(title) values ('San Fransisco');
insert into cities(title) values ('Los Angeles');

create table stores (id serial primary key,
 city integer not null references cities(id),
 title varchar);
insert into stores(city, title) values (1, 'North City');
insert into stores(city, title) values (2, 'Central District');
insert into stores (city, title) values (1, 'Beachfront");

Given the above, and I wanted to know all the tables/records that relate to id
1, San Fransisco, and get a result something like:

table  | primary key
stores | 1
stores | 3

Does such functionality exist in PG? Isn't it already doing essentially this
when attempting to delete a record with other records linked to it?

Currently, I do this by attempting to delete a record in a transaction, and
trap the error - it's a terrible way to do this, and sometimes I'm already in
a transaction.

-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

On Mon, Jan 24, 2005 at 08:35:45PM -0800, Benjamin Smith wrote:

> Is there a way in PG 7.3, given a field, to find out what other tables &
> records are linked to it via a foreign key?

See the "System Catalogs" chapter in the documentation:

http://www.postgresql.org/docs/7.3/static/catalogs.html

The pg_constraint table contains, among other things, foreign key
constraints.  By querying it and joining it with pg_attribute,
pg_class, and pg_namespace, you could get a list of tables and
columns that have foreign key constraints on the given table and
column; from that you could build queries to find out which rows
in those tables match the given value.  You could wrap all this
code in a set-returning function.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr wrote:
> On Mon, Jan 24, 2005 at 08:35:45PM -0800, Benjamin Smith wrote:
>>Is there a way in PG 7.3, given a field, to find out what other tables &
>>records are linked to it via a foreign key?
>
> The pg_constraint table contains, among other things, foreign key
> constraints.  By querying it and joining it with pg_attribute,
> pg_class, and pg_namespace, you could get a list of tables and
> columns that have foreign key constraints on the given table and
> column; from that you could build queries to find out which rows
> in those tables match the given value.  You could wrap all this
> code in a set-returning function.

I just needed such a function yesterday, and wrote one. Here it is, use
it for whatever you want ;-)
create type foreignkey (,
    table_referenced as regclass,
    fields_referenced as varchar[],
    table_referencing as regclass,
    fields_referencing as varchar[]
) ;

create or replace function f_get_pks(regclass) returns foreignkey as '
select
         pg_constraint.confrelid::regclass as table_referenced,
         array(select pg_attribute.attname from pg_catalog.pg_attribute
                 where
                         pg_attribute.attrelid = pg_constraint.confrelid
                         and
                         pg_attribute.attnum = ANY(pg_constraint.confkey)
                 order by alienkey.f_array_pos(
                         pg_constraint.confkey,
                         pg_attribute.attnum
                 )
         )::varchar[] as fields_referenced,
         pg_constraint.conrelid::regclass as table_referencing,
         array(select pg_attribute.attname from pg_catalog.pg_attribute
                 where
                         pg_attribute.attrelid = pg_constraint.conrelid
                         and
                         pg_attribute.attnum = ANY(pg_constraint.conkey)
                 order by alienkey.f_array_pos(
                         pg_constraint.confkey,
                         pg_attribute.attnum
                 )
         )::varchar[] as fields_referencing
from pg_catalog.pg_constraint
where
         pg_constraint.confrelid = $1 and
         pg_constraint.contype = 'f'
' language 'sql' stable ;


Of course this could be a view too - just remove the where-clause
containing "= $1", and wrap it in a create view statement.

greetings, Florian Pflug