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