Subject: SQL to determine Tablenames, Primarykeys & Foreignkeys]
From: Kevin Gordon <kgordon@paradise.net.nz>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 04 Jan 2003 16:57:09 +1300
Message-Id: <1041652653.17139.6.camel@kg15.kgdomain.com>
Mime-Version: 1.0

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