Re: SQL to determine Tablenames, Primarykeys & Foreignkeys

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
>