Received: from webcon.net (guru.webcon.net [216.187.106.140]) by postgresql.org (Postfix) with ESMTP id 37B60475469 for ; Tue, 23 Apr 2002 02:54:31 -0400 (EDT) Received: from light.int.webcon.net (light.int.webcon.net [192.168.1.1]) by webcon.net (8.11.6/8.11.6) with ESMTP id g3N6sdS30019 for ; Tue, 23 Apr 2002 02:54:39 -0400 Date: Tue, 23 Apr 2002 02:54:31 -0400 (EDT) From: Ian Morgan To: pgsql-sql@postgresql.org Subject: How to discover foreign keys (without pulling hair out) Message-ID: Organization: "Webcon, Inc." MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200204/272 X-Sequence-Number: 7536 Given a particular table, I want to get a list of all the foreign keys and what they refer to, something like: local table | local field | foreign table | foreign field -------------+-------------+---------------+--------------- companies | prime | contacts | contact_id companies | referer | contacts | contact_id contacts | company_id | companies | company_id etc.. Here's as far as I've gotten: To find the foreign keys in the "companies" table, do: SELECT tgargs FROM pg_trigger WHERE tgrelid = (select oid from pg_class where relname='companies') AND tgfoid = (select oid from pg_proc where proname='RI_FKey_check_ins') ; tgargs ----------------------------------------------------------------------------- co.pri-c\000companies\000contacts\000UNSPECIFIED\000prime\000contact_id\000 co.ref-c\000companies\000contacts\000UNSPECIFIED\000referer\000contact_id\000 (2 rows) These are the 6 args to the RI_FKey_check_ins function that is called in relation to foreign key checking. 1 = trigger name 2 = local table 3 = foreign table 4 = ? (what is this one?) 5 = local field 6 = foreign field With painstaking use of position(), octet_length(), and substring(), one can extract the appropriate fields. Here's just one: select substring( ( select substring( tgargs, ( position('\\000'::bytea in tgargs) + octet_length('\\000companies\\000'::bytea) ) ) from test limit 1 ) from 1 for ( select position('\\000'::bytea in ( select substring( tgargs, ( position('\\000'::bytea in tgargs) + octet_length('\\000companies\\000'::bytea) ) ) from test limit 1 ) ) ) - 1 ) as foreign_table; foreign_table --------------- contacts (1 row) Obviously, if there's an easier way, I'm all ears. BTW, I would like to avoid custom functions if at all possible. Regards, Ian Morgan -- ------------------------------------------------------------------- Ian E. Morgan Vice President & C.O.O. Webcon, Inc. imorgan@webcon.net PGP: #2DA40D07 www.webcon.net * Customized Linux network solutions for your business * -------------------------------------------------------------------