Thread

1. A Query on PG_ tables

   NRonayette <r97m10@socotec.fr> — 2000-06-19T08:52:20Z

   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.
   
   

2. Re: A Query on PG_ tables

   Stephan Szabo <sszabo@kick.com> — 2000-06-19T18:48:54Z

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

3. Re: A Query on PG_ tables

   NRonayette <r97m10@socotec.fr> — 2000-06-20T07:58:15Z

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