On Thu, Nov 5, 2009 at 2:08 PM, Svenne Krap <svenne.li...@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 > > 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...
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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
