Thread: How to discover foreign keys (without pulling hair out)
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_idcompanies | referer | contacts | contact_idcontacts | 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_procwhere 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 foryour business * -------------------------------------------------------------------
Hi, I use pgAdminII on Windows. It connects to a PostgreSQL on a Linux PC. When you click on an existing table's icon, it shows you a script that could have created it (it detects the structure of the table by itself), including the foreign keys. That's an easy way. But maybe not very elegant... -- Table: slip CREATE TABLE "slip" ( "id_slip" int4 DEFAULT nextval ('"slip_id_slip_seq"'::text) NOT NULL, "proprio" int4, CONSTRAINT "slip_pkey" PRIMARY KEY ("id_slip"), CONSTRAINT "slip_de"FOREIGN KEY ("proprio") REFERENCES "maitre" ("maitre_id") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE ); COMMENT ON TABLE "slip" IS 'les slobards en folie !'; ------------------- Vous aussi devenez le "Coup de coeur de Julie" et gagnez une Webcam sur http://www.chez.com ! Chez.com vous invite � d�couvrir l'univers passionnant des sites persos.
On Thu, 25 Apr 2002, bob lapique wrote: > I use pgAdminII on Windows. It connects to a PostgreSQL on a Linux PC. > When you click on an existing table's icon, it shows you a script that > could have created it (it detects the structure of the table by itself), > including the foreign keys. That's an easy way. But maybe not very > elegant... I have in fact tried that very thing. pgAdminII has a Log View that is supposed to show all the back-end SQL that it executes in order to show you what it does. However, all it really does is retrieve the pg_triggers.tgargs field, which is 6 fields separated by \0's, then it must parse it internally. I'm looking for a way to parse 'one\000two\000three\000'::bytea into foo1 | foo2 | foo3 ------+------+-------one | two | three using only (Postgre)SQL, no external C functions or the like. Ideas? I'm looking into PL/pgSQL as a solution, but haven't gotten very far yet. 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 foryour business * -------------------------------------------------------------------