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
