Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5C894475E7B for ; Fri, 3 Jan 2003 22:30:15 -0500 (EST) Received: from smtp-1.paradise.net.nz (smtp-1b.paradise.net.nz [202.0.32.210]) by postgresql.org (Postfix) with ESMTP id D6634475E64 for ; Fri, 3 Jan 2003 22:30:12 -0500 (EST) Received: from kg15.kgdomain.com (203-79-95-137.apx0.paradise.net.nz [203.79.95.137]) by smtp-1.paradise.net.nz (Postfix) with ESMTP id BB79A831A3 for ; Sat, 4 Jan 2003 16:30:04 +1300 (NZDT) Subject: SQL to determine Tablenames, Primarykeys & Foreignkeys From: Kevin Gordon To: pgsql-php@postgresql.org Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 04 Jan 2003 16:27:26 +1300 Message-Id: <1041650869.16560.11.camel@kg15.kgdomain.com> Mime-Version: 1.0 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/8 X-Sequence-Number: 1753 Thanks to previous help I am using: $sql = "SELECT " . "ic.relname AS index_name, " . "bc.relname AS tab_name, " . "ta.attname AS column_name, " . "i.indisunique AS unique_key, " . "i.indisprimary AS primary_key " . "FROM " . "pg_class bc, " . "pg_class ic, " . "pg_index i, " . "pg_attribute ta, " . "pg_attribute ia " . "WHERE " . "bc.oid = i.indrelid " . "AND ic.oid = i.indexrelid " . "AND ia.attrelid = i.indexrelid " . "AND ta.attrelid = bc.oid " . "AND bc.relname = '" . $tablename . "' " . "AND ta.attrelid = i.indrelid " . "AND ta.attnum = i.indkey[ia.attnum-1] " . "ORDER BY " . "index_name, tab_name, column_name"; which provides primary keys 100%. I have written the following to obtain tablenames: $sql = "SELECT " . "ic.relname " . "FROM " . "pg_class ic " . "WHERE " . "ic.relname not like 'pg%' " . "AND ic.relname not like '%pk' " . "AND ic.relname not like '%idx' "; which I am not certain is complete but appears to work. Could anyone help me with the SQL to retrieve Foreign Keys for a particular Table? Much appreciated. Kevin Gordon