Skip site navigation (1) Skip section navigation (2)

SQL to retrieve FK's, Update/Delete action, etc.

From: Michael Fork <mfork(at)toledolink(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: SQL to retrieve FK's, Update/Delete action, etc.
Date: 2000-12-03 04:22:54
Message-ID: Pine.BSI.4.21.0012022311040.21492-100000@glass.toledolink.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

pgsql-hackers by date

Next:From: Thomas LockhartDate: 2000-12-03 04:42:05
Subject: Re: beta testing version
Previous:From: mlwDate: 2000-12-03 04:03:58
Subject: core dump? OID/database corruption?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group