RE: SQL to retrieve FK's, Update/Delete action, etc. (fwd)

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(at)postgresql(dot)org
> > [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Michael Fork
> > Sent: Sunday, December 03, 2000 12:23 PM
> > To: pgsql-hackers(at)postgresql(dot)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
> >
>