foreign key's in system tables

From: "Tjibbe Rijpma" <tjibbe(at)hotmail(dot)com>
To:
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: foreign key's in system tables
Date: 2006-01-05 07:57:45
Message-ID: BAY115-DAV882CE214C96F40B3A2C53DD2F0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

From a table I want to know all the foreign key en their references. This goes well, but if there is more than one column in a FOREIGN KEY is goes wrong.
I have trouble with the arrays: conkey and confkey.

Can anyone help me?

Greetings Tjibbe

CREATE TABLE mens (
voornaam TEXT,
achternaam TEXT,
UNIQUE (voornaam, achternaam)
);

CREATE TABLE inwoner (
inwoner_voornaam TEXT,
inwoner_achternaam TEXT,
FOREIGN KEY (inwoner_voornaam, inwoner_achternaam) REFERENCES mens (voornaam, achternaam)
);

SELECT a_fk.attname as fk_column, ref.relname as ref_table, a_ref.attname as ref_column
FROM pg_constraint c, pg_class fk, pg_attribute a_fk, pg_class ref, pg_attribute a_ref
WHERE c.conrelid = fk.oid
AND a_fk.attrelid = fk.oid
AND c.confrelid = ref.oid
AND a_ref.attrelid = ref.oid
AND c.contype = 'f'
AND fk.relname = 'inwoner'
AND a_fk.attnum = ANY (c.conkey)
AND a_ref.attnum = ANY (c.confkey)

Result:

fk_column | ref_table | ref_column
--------------------------------------------
inwoner_voornaam | mens | voornaam
inwoner_voornaam | mens | achternaam
inwoner_achternaam | mens | voornaam
inwoner_achternaam | mens | achternaam

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Andrew Chambers 2006-01-05 08:25:17 Re: GRANT access rights on rows
Previous Message Yumiko Izumi 2006-01-05 06:53:56 Re: We want to monitor total size of database