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