Re: 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(at)postgresql(dot)org
> >
>
>