Thread: SQL to determine Tablenames, Primarykeys & Foreignkeys

SQL to determine Tablenames, Primarykeys & Foreignkeys

From

Kevin Gordon

Date:

04 January 2003, 01:30:16

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

Re: SQL to determine Tablenames, Primarykeys & Foreignkeys

From

Christopher Kings-Lynne

Date:

04 January 2003, 03:45:23

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
>

Re: SQL to determine Tablenames, Primarykeys & Foreignkeys

From

Kevin Gordon

Date:

04 January 2003, 20:45:56

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

Re: SQL to determine Tablenames, Primarykeys & Foreignkeys

From

"Christopher Kings-Lynne"

Date:

06 January 2003, 01:12:13

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