Message-ID: <4AF33EB9.4060901@krap.dk>
Date: Thu, 05 Nov 2009 22:08:09 +0100
From: Svenne Krap <svenne.lists@krap.dk>
User-Agent: Thunderbird 2.0.0.23 (X11/20091022)
MIME-Version: 1.0
To: sql pgsql <pgsql-sql@postgresql.org>
Subject: Foreign key columns
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Hi.

Is there a simple way to get foreign key data... for example I found a
view, that does what I want ...

It delivers

   fk_table   |     fk_column      |     pk_table      | pk_column |           constraint_name

--------------+--------------------+-------------------+-----------+--------------------------------------

 organisation | customer_rep       | person            | id        | organisation_customer_rep_fkey

 organisation | ekstra_skema       | ekstra_skema      | id        | org_schema_fkey

 organisation | in_group           | organisation      | id        | organisation_in_group_fkey

 organisation | org_paying_company | organisation      | id        | organisation_org_paying_company_fkey

 organisation | primary_contact    | person            | id        | primary_contact_fkey

 organisation | type               | organisation_type | id        | organisation_type_fkey


The query in question is

SELECT FK.TABLE_NAME as FK_Table, CU.COLUMN_NAME as FK_Column,
PK.TABLE_NAME as PK_Table, PT.COLUMN_NAME as PK_Column,
C.CONSTRAINT_NAME as Constraint_Name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME
= FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME =
CU.CONSTRAINT_NAME
INNER JOIN (
     SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME =
PK.TABLE_NAME WHERE FK.TABLE_NAME='organisation' ORDER BY 1,2,3,4;


The only problem is that this query is sloooooow, runs in tens of seconds...


Is there a good native (i.e. fast) pgsql-query to find that type of
information?

Svenne