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