Re: [GENERAL] Search for restricting foreign keys

[Florian G. Pflug]

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

smime.p7s
Description: S/MIME Cryptographic Signature