Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 82B88475CC7 for ; Sat, 4 Jan 2003 17:45:55 -0500 (EST) Received: from smtp-3.paradise.net.nz (smtp-3b.paradise.net.nz [202.0.32.212]) by postgresql.org (Postfix) with ESMTP id A55F4475B33 for ; Sat, 4 Jan 2003 17:45:54 -0500 (EST) Received: from kg15.kgdomain.com (203-79-68-43.apx0.paradise.net.nz [203.79.68.43]) by smtp-3.paradise.net.nz (Postfix) with ESMTP id B6AE3AE4A6; Sun, 5 Jan 2003 11:45:36 +1300 (NZDT) Subject: Re: SQL to determine Tablenames, Primarykeys & Foreignkeys From: Kevin Gordon To: Christopher Kings-Lynne Cc: pgsql-php@postgresql.org In-Reply-To: <20030104134427.R36192-100000@houston.familyhealth.com.au> References: <20030104134427.R36192-100000@houston.familyhealth.com.au> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 05 Jan 2003 11:42:53 +1300 Message-Id: <1041720206.9281.11.camel@kg15.kgdomain.com> Mime-Version: 1.0 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/10 X-Sequence-Number: 1755 Thanks Chris. I am not certain that this actually lists foreign keys: kgdb=# SELECT t.tgname kgdb-# FROM pg_trigger t, pg_class c kgdb-# WHERE c.relname='menu_lines' AND c.oid = t.tgrelid; tgname ---------------------------- RI_ConstraintTrigger_16675 (1 row) Does \d Tablename indicate which fields are primary keys and which fields are Foreign Keys? On Sat, 2003-01-04 at 18:45, Christopher Kings-Lynne wrote: > Hi Kevin, > > Run "psql -E" and connect to your database. > > Then go "\d mytable" and you will see all the correct SQL for getting fk's > and etc. Note that it's only really feasible to get fk's in 7.3. > > Chriu > > On 4 Jan 2003, Kevin Gordon wrote: > > > 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 > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > >