From: Ashish Karalkar <ashish.karalkar@info-spectrum.com>
To: Andreas <maps.on@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: List of FKeys ?
Date: Mon, 27 Aug 2007 16:48:44 +0530
Message-ID: <007701c7e89c$15bf0190$170211ac@LIONKING.COM> (raw)
References: <46D20E56.10101@gmx.net>
<12827.1188186512@sss.pgh.pa.us>
----- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ashish.karalkar@info-spectrum.com, maps.on@gmx.net, tgl@sss.pgh.pa.us
Subject: Re: List of FKeys ?
In-Reply-To: <007701c7e89c$15bf0190$170211ac@LIONKING.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox