pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SQL to determine Tablenames, Primarykeys & Foreignkeys
4+ messages / 2 participants
[nested] [flat]

* SQL to determine Tablenames, Primarykeys & Foreignkeys
@ 2003-01-04 03:27  Kevin Gordon <kgordon@paradise.net.nz>
  0 siblings, 1 reply; 4+ messages in thread

From: Kevin Gordon @ 2003-01-04 03:27 UTC (permalink / raw)
  To: pgsql-php

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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: SQL to determine Tablenames, Primarykeys & Foreignkeys
@ 2003-01-04 05:45  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Kevin Gordon <kgordon@paradise.net.nz>
  0 siblings, 1 reply; 4+ messages in thread

From: Christopher Kings-Lynne @ 2003-01-04 05:45 UTC (permalink / raw)
  To: Kevin Gordon <kgordon@paradise.net.nz>; +Cc: pgsql-php

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
>




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: SQL to determine Tablenames, Primarykeys & Foreignkeys
@ 2003-01-04 22:42  Kevin Gordon <kgordon@paradise.net.nz>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 4+ messages in thread

From: Kevin Gordon @ 2003-01-04 22:42 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-php

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: SQL to determine Tablenames, Primarykeys & Foreignkeys
@ 2003-01-06 03:12  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Kevin Gordon <kgordon@paradise.net.nz>
  0 siblings, 0 replies; 4+ messages in thread

From: Christopher Kings-Lynne @ 2003-01-06 03:12 UTC (permalink / raw)
  To: Kevin Gordon <kgordon@paradise.net.nz>; +Cc: pgsql-php

It will (on 7.2) but it will also include all other triggers.  I'm telling
you right now that getting foreign keys from trigger info on 7.2 and below
is an unholy nightmare.

Basically all a FK is is a trigger on the child table and two on the parent.

Chris

> -----Original Message-----
> From: Kevin Gordon [mailto:kgordon@paradise.net.nz]
> Sent: Sunday, 5 January 2003 6:43 AM
> To: Christopher Kings-Lynne
> Cc: pgsql-php@postgresql.org
> Subject: Re: [PHP] SQL to determine Tablenames, Primarykeys &
> Foreignkeys
>
>
> 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
> > >
> >
> >
>
>




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2003-01-06 03:12 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-04 03:27 SQL to determine Tablenames, Primarykeys & Foreignkeys Kevin Gordon <kgordon@paradise.net.nz>
2003-01-04 05:45 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-01-04 22:42   ` Kevin Gordon <kgordon@paradise.net.nz>
2003-01-06 03:12     ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>

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