Message-ID: <007701c7e89c$15bf0190$170211ac@LIONKING.COM>
From: "Ashish Karalkar" <ashish.karalkar@info-spectrum.com>
To: "Andreas" <maps.on@gmx.net>, "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-sql@postgresql.org>
References: <46D20E56.10101@gmx.net> <12827.1188186512@sss.pgh.pa.us>
Subject: Re: List of FKeys ? 
Date: Mon, 27 Aug 2007 16:48:44 +0530
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit


----- 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;
 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