Can you help me with this query?

From: mike(dot)griffin(at)mygenerationsoftware(dot)com
To: "postgresql" <pgsql-general(at)postgresql(dot)org>
Subject: Can you help me with this query?
Date: 2004-06-15 01:48:33
Message-ID: 4238.4.160.192.46.1087264113.squirrel@4.160.192.46
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

The query below will return all of the foreign keys in the current schema,
I get the ForeignKey name as FK_NAME and both the primary and foreign
table's name and schema, now I just need the columns involved in the
foreign key itself, there is a column called confkey in pg_constraint and
it's an array? It holds the column id

SELECT ct.oid, conname as FK_NAME, confkey, nl.nspname as PK_TABLE_SCHEMA,
cl.relname as PK_TABLE_NAME,
nr.nspname as FK_TABLE_SCHEMA, cr.relname as FK_TABLE_NAME, description
FROM pg_constraint ct
JOIN pg_class cl ON cl.oid=conrelid
JOIN pg_namespace nl ON nl.oid=cl.relnamespace
JOIN pg_class cr ON cr.oid=confrelid
JOIN pg_namespace nr ON nr.oid=cr.relnamespace
LEFT OUTER JOIN pg_description des ON des.objoid=ct.oid
WHERE contype='f'

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Allison 2004-06-15 01:51:34 Re: perl access
Previous Message Joshua D. Drake 2004-06-15 01:20:14 Re: perl access