Skip site navigation (1) Skip section navigation (2)

Re: Foreign Keys einer Tabelle herausfinden

From: Andreas 'ads' Scherbaum <adsmail(at)wars-nicht(dot)de>
To: postgres-liste <pgsql-de-allgemein(at)postgresql(dot)org>
Subject: Re: Foreign Keys einer Tabelle herausfinden
Date: 2009-09-07 21:36:48
Message-ID: 20090907233648.4ab3f769@iridium.wars-nicht.de (view raw or flat)
Thread:
Lists: pgsql-de-allgemein
Hallo,

On Mon, 07 Sep 2009 23:22:19 +0200 Philipp Lawitschka wrote:

> habs schon herausgefunden...wen es interessiert:
> 
> SELECT c.conname, c.conkey[1] AS MyColumnID, c.confkey[1] AS 			 
> ForeignColumnID, tab1.relname AS MyTable,
>         tab2.relname AS ForeignTable, a1.attname AS MyColumn,
>         a2.attname AS ForeignColumn
> FROM pg_constraint c
> 	INNER JOIN pg_class tab1 ON c.conrelid = tab1.oid
> 	INNER JOIN pg_class tab2 ON c.confrelid = tab2.oid
> 	INNER JOIN pg_attribute a1 ON c.conkey[1] = a1.attnum
> 	INNER JOIN pg_attribute a2 ON c.confkey[1] = a2.attnum
> WHERE c.contype = 'f' AND a1.attrelid = tab1.oid AND a2.attrelid = tab2.oid

Wäre es nicht praktischer, das portabel über verschiedene Versionen
hinweg zu gestalten? Der View:

information_schema.referential_constraints

enthält die Informationen, die du suchst. Die Systemtabellen
können sich von Version zu Version ändern, das information_schema
bleibt gleich.


Bis dann

-- 
				Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

PGDay.eu 2009 in Paris, Nov. 6/7, http://www.pgday.eu/

In response to

pgsql-de-allgemein by date

Next:From: Andreas 'ads' ScherbaumDate: 2009-09-07 23:43:04
Subject: == Wöchentlicher PostgreSQL Newsletter - 06. September 2009 ==
Previous:From: Philipp LawitschkaDate: 2009-09-07 21:22:19
Subject: Re: Foreign Keys einer Tabelle herausfinden

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group