Re: array support patch phase 1 patch

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