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: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Fork > > Sent: Sunday, December 03, 2000 12:23 PM > > To: [EMAIL PROTECTED] > > 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 > > >
