pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kevin Gordon <kgordon@paradise.net.nz>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-php@postgresql.org
Subject: Re: SQL to determine Tablenames, Primarykeys & Foreignkeys
Date: 05 Jan 2003 11:42:53 +1300
Message-ID: <1041720206.9281.11.camel@kg15.kgdomain.com> (raw)
In-Reply-To: <20030104134427.R36192-100000@houston.familyhealth.com.au>
References: <20030104134427.R36192-100000@houston.familyhealth.com.au>

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
> >
> 
> 





view thread (4+ messages)  latest in thread

Message-ID: <1041720206.9281.11.camel@kg15.kgdomain.com>
Permalink:  ../1041720206.9281.11.camel@kg15.kgdomain.com/
Also on:    postgresql.org/message-id/1041720206.9281.11.camel@kg15.kgdomain.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: kgordon@paradise.net.nz, chriskl@familyhealth.com.au
  Subject: Re: SQL to determine Tablenames, Primarykeys & Foreignkeys
  In-Reply-To: <1041720206.9281.11.camel@kg15.kgdomain.com>
  References: <20030104134427.R36192-100000@houston.familyhealth.com.au> <1041720206.9281.11.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