Re: List of FKeys ?

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


> Andreas <maps.on@gmx.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;BEGINpar_result :='Successfull';       var_str1:='select
distinct(fk_relation),fk_columnfrom 
 
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:='';whilefound ='t' loop          FETCH  curforeign  into tablename,columnname ;
var_str2:='selectcount(*) 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