Re: List of FKeys ?

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Andreas" <maps(dot)on(at)gmx(dot)net>
Cc: <pgsql-sql(at)postgresql(dot)org>
Sent: Monday, August 27, 2007 9:18 AM
Subject: Re: [SQL] List of FKeys ?

> Andreas <maps(dot)on(at)gmx(dot)net> writes:
>> could I get a list of foreign keys that refer to a column?
>
> The information_schema views constraint_column_usage and
> referential_constraints might help you, or you could dive into the
> underlying system catalogs.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Hey Andreas is ur problem is not solved use following SP, I use it for the
same reason.

just pass the primary key column name and primary key value it will return u
list of child table's

sp_gen_foreign_keys_tables(OUT par_result text, OUT par_childtables text, IN
par_colname character varying, IN par_colvalue integer) AS
$BODY$

DECLARE
err_data_entity varchar(100) default 'To find child records ';
err_operation varchar(100) default 'Select';
curforeign refcursor ;
curforeign1 refcursor;
tablename text;
columnname text;
var_str1 text;
var_str2 text;
countno integer;
counter integer;
BEGIN
par_result :='Successfull';
var_str1:='select distinct(fk_relation),fk_column from
core_foreign_keys_view where pk_relation in (select pk_relation from
core_foreign_keys_view where pk_column='''|| par_colname||''')';
open curforeign for execute var_str1;
found:='true';
par_childtables:='';
while found ='t' loop
FETCH curforeign into tablename,columnname ;
var_str2:='select count(*) from '|| tablename || ' where ' || columnname
||' = '|| par_colvalue;
IF VAR_STR2 IS NULL THEN
EXIT;
END IF;
open curforeign1 for execute var_str2;
FETCH curforeign1 into countno;
close curforeign1;
if countno > 0 then
par_childtables:=par_childtables || tablename||'.'||columnname|| ',' ;
end if ;

end loop;
close curforeign ;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Hope this will help

With Regards
Ashish