From: | "Florian G(dot) Pflug" <fgp(at)phlo(dot)org> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | Benjamin Smith <lists(at)benjamindsmith(dot)com>, PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Search for restricting foreign keys |
Date: | 2005-01-25 19:00:58 |
Message-ID: | 41F6976A.3020709@phlo.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | John DeSoi | 2005-01-25 19:11:07 | Re: Restaurando una base de datos |
Previous Message | felix | 2005-01-25 19:00:22 | Re: Postgresql, SQL server and Oracle. Please, help |