RE: SQL to retrieve FK's, Update/Delete action, etc. (fwd)
От | Michael Fork |
---|---|
Тема | RE: SQL to retrieve FK's, Update/Delete action, etc. (fwd) |
Дата | |
Msg-id | Pine.BSI.4.21.0012050935170.16721-100000@glass.toledolink.com обсуждение исходный текст |
Список | 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 > > > > > > > > > >
В списке pgsql-hackers по дате отправления: