DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=ZAG0fIqtTMFN/RJZIq/2vycilbUy9V8lzM1TnwrjsofdDHkokph7+kVEKaMRhSOgTn
	Ltr5G8rh2xdrbD8MMIZ2lf6DWmVQiSWnT/EJ4Ic2U19W1W9obZdvzfs0obkv8ogKPDgl
	aANVMR4M4R48vj/bUjLeGtRarGjqGGS3NIJgc=
MIME-Version: 1.0
In-Reply-To: <4AF33EB9.4060901@krap.dk>
References: <4AF33EB9.4060901@krap.dk>
Date: Thu, 5 Nov 2009 15:21:50 -0700
Message-ID: <dcc563d10911051421m5375cf6fx3434d049b19f58b1@mail.gmail.com>
Subject: Re: Foreign key columns
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Svenne Krap <svenne.lists@krap.dk>
Cc: sql pgsql <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Thu, Nov 5, 2009 at 2:08 PM, Svenne Krap <svenne.lists@krap.dk> wrote:
> Hi.
>
> Is there a simple way to get foreign key data... for example I found a
> view, that does what I want ...
>
> It delivers
>
> =A0 fk_table =A0 | =A0 =A0 fk_column =A0 =A0 =A0| =A0 =A0 pk_table =A0 =
=A0 =A0| pk_column | =A0 =A0 =A0 =A0 =A0 constraint_name
>
> --------------+--------------------+-------------------+-----------+-----=
---------------------------------
>
> =A0organisation | customer_rep =A0 =A0 =A0 | person =A0 =A0 =A0 =A0 =A0 =
=A0| id =A0 =A0 =A0 =A0| organisation_customer_rep_fkey
>
> =A0organisation | ekstra_skema =A0 =A0 =A0 | ekstra_skema =A0 =A0 =A0| id=
 =A0 =A0 =A0 =A0| org_schema_fkey
>
> =A0organisation | in_group =A0 =A0 =A0 =A0 =A0 | organisation =A0 =A0 =A0=
| id =A0 =A0 =A0 =A0| organisation_in_group_fkey
>
> =A0organisation | org_paying_company | organisation =A0 =A0 =A0| id =A0 =
=A0 =A0 =A0| organisation_org_paying_company_fkey
>
> =A0organisation | primary_contact =A0 =A0| person =A0 =A0 =A0 =A0 =A0 =A0=
| id =A0 =A0 =A0 =A0| primary_contact_fkey
>
> =A0organisation | type =A0 =A0 =A0 =A0 =A0 =A0 =A0 | organisation_type | =
id =A0 =A0 =A0 =A0| 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
> =3D FK.CONSTRAINT_NAME
> INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
> C.UNIQUE_CONSTRAINT_NAME =3D PK.CONSTRAINT_NAME
> INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME =
=3D
> CU.CONSTRAINT_NAME
> INNER JOIN (
> =A0 =A0 SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
> =A0 =A0 =A0 =A0INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
> i1.CONSTRAINT_NAME =3D i2.CONSTRAINT_NAME
> =A0 =A0 =A0 =A0WHERE i1.CONSTRAINT_TYPE =3D 'PRIMARY KEY') PT ON PT.TABLE=
_NAME =3D
> PK.TABLE_NAME WHERE FK.TABLE_NAME=3D'organisation' ORDER BY 1,2,3,4;
>
>
> The only problem is that this query is sloooooow, runs in tens of seconds=
...

It runs in 112 milliseconds on my machine.  Maybe your catalogs are
extremely bloated?

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

This one seems to work pretty well.  If you want to see a query to
find such things, the easy way is to start psql with the -E switch,
and issue a \d command on the organisation table and steal the SQL
from there.  That query will be pgsql specific, and possibly / likely
pgsql VERSION dependent, so know that going into it.