I've been looking at using the new array support with the JDBC driver to
retrieve foreign key information and I've kind of gotten stuck.
The basic query I want to run is
SELECT
pkn.nspname AS PKTABLE_SCHEM,
pkt.relname AS PKTABLE_NAME,
pka.attname AS PKCOLUMN_NAME,
fkn.nspname AS FKTABLE_SCHEM,
fkt.relname AS FKTABLE_NAME,
fka.attname AS FKCOLUMN_NAME,
c.conname AS FK_NAME,
pkc.conname AS PK_NAME
FROM pg_namespace pkn, pg_class pkt, pg_attribute pka,
pg_namespace fkn, pg_class fkt, pg_attribute fka,
pg_constraint c, pg_constraint pkc
WHERE
pkn.oid = pkt.relnamespace
AND pkt.oid = pka.attrelid
AND fkn.oid = fkt.relnamespace
AND fkt.oid = fka.attrelid
AND c.conrelid = fkt.oid
AND c.confrelid = pkt.oid
AND pka.attnum = ANY (c.confkey)
AND fka.attnum = ANY (c.conkey)
AND c.confrelid = pkc.conrelid
-- AND pkc.conkey = c.confkey
;
So I'm getting back the right column and table names, but for a
multi-column key you get a cartesian product because you can't join on
index(conkey) = index(confkey).
I was trying formulate a way to make a function which will explode an
array into a resultset composed of the index and value. So '{3,4,7}'
would become
index value
1 3
2 4
3 7
I suppose you'd really want something like:
CREATE TABLE t (
a int primary key,
b int[]
);
SELECT * FROM explode_index(t,a,b);
returning rows of a, b-index, b-value
Another unrelated issue I ran into was that I wanted an equality operator
that was not ordered, so [1,2,3] = [2,1,3] because they contain the same
elements.
Just one user's thoughts,
Kris Jurka
