Re: A Query on PG_ tables

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