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
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 |