RE: SQL to retrieve FK's, Update/Delete action, etc. (fwd) - Mailing list pgsql-hackers

Here's the query that, given the primary key table, lists all foreign
keys, their tables, the RI type, and defereability.

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

SELECT pg_trigger.tgargs,
pg_trigger.tgnargs,
pg_trigger.tgdeferrable,
pg_trigger.tginitdeferred,
pg_proc.proname,
pg_proc_1.proname
FROM pg_class pg_class,
pg_class pg_class_1,
pg_class pg_class_2,
pg_proc pg_proc,
pg_proc pg_proc_1,
pg_trigger pg_trigger,
pg_trigger pg_trigger_1,
pg_trigger pg_trigger_2
WHERE pg_trigger.tgconstrrelid = pg_class.oid
AND pg_trigger.tgrelid = pg_class_1.oid
AND pg_trigger_1.tgfoid = pg_proc_1.oid
AND pg_trigger_1.tgconstrrelid = pg_class_1.oid
AND pg_trigger_2.tgconstrrelid = pg_class_2.oid
AND pg_trigger_2.tgfoid = pg_proc.oid
AND pg_class_2.oid = pg_trigger.tgrelid
AND ((pg_class.relname='<<PRIMARY KEY TABLE>>')
AND  (pg_proc.proname Like '%upd')
AND  (pg_proc_1.proname Like '%del')
AND (pg_trigger_1.tgrelid=pg_trigger.tgconstrrelid)
AND (pg_trigger_2.tgrelid = pg_trigger.tgconstrrelid))

On Tue, 5 Dec 2000, Christopher Kings-Lynne wrote:

> Thanks mike - chances are it will be committed to phpPgAdmin by the end of
> the week!
> 
> BTW, you may wish to make sure that your email as cc'd to the hacker's list
> as well.
> 
> Regards,
> 
> Chris
> 
> --
> Christopher Kings-Lynne
> Family Health Network (ACN 089 639 243)
> 
> > -----Original Message-----
> > From: Michael Fork [mailto:mfork@toledolink.com]
> > Sent: Tuesday, December 05, 2000 12:25 PM
> > To: Christopher Kings-Lynne
> > Subject: RE: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc.
> >
> >
> > There ya go, I figured it out :)  Given the name a table, this query will
> > return all foreign keys in that table, the table the primary key is in,
> > the name of the primary key, if the are deferrable, if the are initially
> > deffered, and the action to be performed (RESTRICT, SET NULL, etc.).  To
> > get the foreign keys and primary keys and tables, you must parse the
> > null-terminated pg.tgargs.
> >
> > When I get the equivalent query working for primary keys I will send it
> > your way -- or if you beat me to it, send it my way (I am working on some
> > missing functionality from the ODBC driver)
> >
> > Michael Fork - CCNA - MCP - A+
> > Network Support - Toledo Internet Access - Toledo Ohio
> >
> > SELECT pt.tgargs,
> > pt.tgnargs,
> > pt.tgdeferrable,
> > pt.tginitdeferred,
> > pg_proc.proname,
> > pg_proc_1.proname
> > FROM pg_class pc,
> > pg_proc pg_proc,
> > pg_proc pg_proc_1,
> > pg_trigger pg_trigger,
> > pg_trigger pg_trigger_1,
> > pg_proc pp,
> > pg_trigger pt
> > WHERE  pt.tgrelid = pc.oid
> > AND pp.oid = pt.tgfoid
> > AND pg_trigger.tgconstrrelid = pc.oid
> > AND pg_proc.oid = pg_trigger.tgfoid
> > AND pg_trigger_1.tgfoid = pg_proc_1.oid
> > AND pg_trigger_1.tgconstrrelid = pc.oid
> > AND ((pc.relname='<<FOREIGN TABLE>>')
> > AND (pp.proname LIKE '%%ins')
> > AND (pg_proc.proname LIKE '%%upd')
> > AND (pg_proc_1.proname LIKE '%%del')
> > AND (pg_trigger.tgrelid=pt.tgconstrrelid)
> > AND (pg_trigger_1.tgrelid = pt.tgconstrrelid))
> >
> >
> > On Tue, 5 Dec 2000, Christopher Kings-Lynne wrote:
> >
> > > Hi Michael,
> > >
> > > I am on the phpPgAdmin development team, and I have been
> > wanting to add this
> > > functionality to phpPgAdmin.  I will start working with your
> > query as soon
> > > as possible, and I will use phpPgAdmin as a testbed for the
> > functionality.
> > >
> > > I really appreciate having your query as a working basis, because it's
> > > really hard trying to figure out the system tables!
> > >
> > > Chris
> > >
> > > > -----Original Message-----
> > > > From: pgsql-hackers-owner@postgresql.org
> > > > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Michael Fork
> > > > Sent: Sunday, December 03, 2000 12:23 PM
> > > > To: pgsql-hackers@postgresql.org
> > > > Subject: [HACKERS] SQL to retrieve FK's, Update/Delete action, etc.
> > > >
> > > >
> > > > Given the name of a table, I need to find all foreign keys in
> > that table
> > > > and the table/column that they refer to, along with the action to be
> > > > performed on update/delete.  The following query works, but only when
> > > > there is 1 foreign key in the table, when there is more than
> > 2 it grows
> > > > exponentially -- which means I am missing a join.  However, given my
> > > > limitied knowledge about the layouts of the postgres system
> > tables, and
> > > > the pg_trigger not being documented on the web site, I have
> > been unable to
> > > > get the correct query.  Is this possible, and if so, what join(s) am I
> > > > missing?
> > > >
> > > > SELECT pt.tgargs,
> > > > pt.tgnargs,
> > > > pt.tgdeferrable,
> > > > pt.tginitdeferred,
> > > > pg_proc.proname,
> > > > pg_proc_1.proname
> > > > FROM pg_class pc,
> > > > pg_proc pg_proc,
> > > > pg_proc pg_proc_1,
> > > > pg_trigger pg_trigger,
> > > > pg_trigger pg_trigger_1,
> > > > pg_proc pp,
> > > > pg_trigger pt
> > > > WHERE pt.tgrelid = pc.oid
> > > > AND pp.oid = pt.tgfoid
> > > > AND pg_trigger.tgconstrrelid = pc.oid
> > > > AND pg_proc.oid = pg_trigger.tgfoid
> > > > AND pg_trigger_1.tgfoid = pg_proc_1.oid
> > > > AND pg_trigger_1.tgconstrrelid = pc.oid
> > > > AND ((pc.relname='tblmidterm')
> > > > AND (pp.proname LIKE '%ins')
> > > > AND (pg_proc.proname LIKE '%upd')
> > > > AND (pg_proc_1.proname LIKE '%del'))
> > > >
> > > > Michael Fork - CCNA - MCP - A+
> > > > Network Support - Toledo Internet Access - Toledo Ohio
> > > >
> > >
> >
>