pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedList of FKeys ?
3+ messages / 3 participants
[nested] [flat]
* List of FKeys ?
@ 2007-08-26 23:35 Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Andreas @ 2007-08-26 23:35 UTC (permalink / raw)
To: pgsql-sql
Hi,
could I get a list of foreign keys that refer to a column?
Say I have a table_1 (t1_id integer ......)
and a lot of other tables that may refer to table 1.
Is there a command that lists all tables that have a foreign key that
points to t1_id?
Preferably with the "on update/delete" options of the relation.
Regards
Andreas
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: List of FKeys ?
@ 2007-08-27 03:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-08-27 03:48 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: List of FKeys ?
@ 2007-08-27 11:18 Ashish Karalkar <ashish.karalkar@info-spectrum.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Ashish Karalkar @ 2007-08-27 11:18 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
----- 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-08-27 11:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-26 23:35 List of FKeys ? Andreas <maps.on@gmx.net>
2007-08-27 03:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-27 11:18 ` Ashish Karalkar <ashish.karalkar@info-spectrum.com>
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