A Query on PG_ tables
Hi everybody,
I'm using Postgresql 7.0.
I want to find, by a query on the postgres table (like pg_class,
pg_attribute, pg_trigger, etc....), the name of all the colonnes that
are foreign key on my base.
Pg_trigger gives me some information about the foreigns key i added on
my tables (name, id etc..) but i don't know how to join this information
with another pg_<table> (for example, pg_attribute or else) to find the
name of my colonnes that are foreign key.
Someone have an idear ?
I hope i was clear with my poor English...
Thanks for your answers.
Nicolas.
Okay, this gets a little wierd, but, for now:
What you'll probably want is
select proname, tgargs from pg_proc, pg_trigger where
pg_proc.oid=pg_trigger.tgfoid and tgisconstraint=1;
Each properly defined foreign key constraint will have three triggers.
The proname for foreign key constraints will look like on of the following:
RI_FKey_check_ins
RI_FKey_<action>_del - action for deleting a row on the referenced table
RI_FKey_<action>_upd - action for updating a row on the referenced table
The tgargs is layed out as follows:
<constraint name>\000<referencing table>\000<referenced table>\000
<match type>\000<referencing col1>\000<referenced col1>\000
[<referencing col2>\000<referenced col2>\000...]
----- Original Message -----
From: "NRonayette" <r97m10@socotec.fr>
To: <pgsql-general@postgresql.org>
Sent: Monday, June 19, 2000 1:52 AM
Subject: [GENERAL] A Query on PG_ tables
Show quoted text
Hi everybody,
I'm using Postgresql 7.0.
I want to find, by a query on the postgres table (like pg_class,
pg_attribute, pg_trigger, etc....), the name of all the colonnes that
are foreign key on my base.Pg_trigger gives me some information about the foreigns key i added on
my tables (name, id etc..) but i don't know how to join this information
with another pg_<table> (for example, pg_attribute or else) to find the
name of my colonnes that are foreign key.Someone have an idear ?
I hope i was clear with my poor English...
Thanks for your answers.
Nicolas.
Stephan Szabo a �crit :
Okay, this gets a little wierd, but, for now:
What you'll probably want is
select proname, tgargs from pg_proc, pg_trigger where
pg_proc.oid=pg_trigger.tgfoid and tgisconstraint=1;
Thanks for your answer Stephan.
I saw thoses columns and tables in postgres. Pg_trigger.tgargs could
help me because there is the referenced columns in it.
Is this table could be join with another one, like pg_attribute, to
built a "simple" query ?
something like that :
select <referencing table>, <referenced table>, <referencing col1>,
<referenced col1>
from pg_trigger, pg_?
where <referencing table> = mytable_1
and <referenced table> = mytable_2 and pg_trigger.?=pg_?.?
If this could not be possible, i think the only way i have is to analyse
tgargs, find the "\000" and make some substring of tgargs.
Nicolas
Show quoted text
Each properly defined foreign key constraint will have three triggers.
The proname for foreign key constraints will look like on of the following:
RI_FKey_check_ins
RI_FKey_<action>_del - action for deleting a row on the referenced table
RI_FKey_<action>_upd - action for updating a row on the referenced tableThe tgargs is layed out as follows:
<constraint name>\000<referencing table>\000<referenced table>\000
<match type>\000<referencing col1>\000<referenced col1>\000
[<referencing col2>\000<referenced col2>\000...]----- Original Message -----
From: "NRonayette" <r97m10@socotec.fr>
To: <pgsql-general@postgresql.org>
Sent: Monday, June 19, 2000 1:52 AM
Subject: [GENERAL] A Query on PG_ tablesHi everybody,
I'm using Postgresql 7.0.
I want to find, by a query on the postgres table (like pg_class,
pg_attribute, pg_trigger, etc....), the name of all the colonnes that
are foreign key on my base.Pg_trigger gives me some information about the foreigns key i added on
my tables (name, id etc..) but i don't know how to join this information
with another pg_<table> (for example, pg_attribute or else) to find the
name of my colonnes that are foreign key.Someone have an idear ?
I hope i was clear with my poor English...
Thanks for your answers.
Nicolas.