----- Original Message -----
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Andreas" <[EMAIL PROTECTED]>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, August 27, 2007 9:18 AM
Subject: Re: [SQL] List of FKeys ?
Andreas <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
