From: Kevin Gordon <kgordon@paradise.net.nz>
To: pgsql-sql@postgresql.org
Subject: SQL to determine Tablenames, Primarykeys & Foreignkeys]
Date: 04 Jan 2003 16:57:09 +1300
Message-ID: <1041652653.17139.6.camel@kg15.kgdomain.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kgordon@paradise.net.nz
Subject: Re: SQL to determine Tablenames, Primarykeys & Foreignkeys]
In-Reply-To: <1041652653.17139.6.camel@kg15.kgdomain.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox