Retrieving multidimentional array-column using JDBC

From: Andreas Joseph Krogh <andreak(at)officenet(dot)no>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Retrieving multidimentional array-column using JDBC
Date: 2010-03-09 23:39:14
Message-ID: 201003100039.14562.andreak@officenet.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi all.

I have a query which returns a multidimentional array (2D) of varchar and I'm having problems retrieving the results using JDBC.

First, since PG doesn't support arrays of arrays, I've created a custom type:

create type text_array as (c TEXT[]);

Then I have the following schema:

--=================8<==================

CREATE TABLE foo(
id integer primary key
);

CREATE TABLE pathway(
id integer primary key,
p_id integer references pathway(id),
element varchar
);

CREATE TABLE path_usage(
foo_id integer references foo(id),
pathway_id integer references pathway(id)
);

INSERT INTO foo VALUES(1);

insert into pathway(id, p_id, element) values(1, null, 'path');
insert into pathway(id, p_id, element) values(2, 1, 'to');
insert into pathway(id, p_id, element) values(3, 2, 'first');
insert into pathway(id, p_id, element) values(4, 2, 'second');

insert into path_usage(foo_id, pathway_id) values(1, 3);
insert into path_usage(foo_id, pathway_id) values(1, 4);

--=================8<==================

I use this query to retrieve a list of all "foo"s with their (complete) paths:

SELECT act.id, (SELECT array_agg(row(pathway_array)::text_array) FROM
(SELECT ARRAY(
WITH RECURSIVE parent(level, id, p_id, element) AS(
SELECT 1, pw.id, pw.p_id, pw.element
UNION ALL
SELECT rec.level + 1, pw_.id, pw_.p_id, pw_.element
FROM pathway pw_, parent rec
WHERE pw_.id = rec.p_id
) SELECT element FROM parent ORDER BY level DESC
) AS pathway_array
FROM pathway pw, path_usage pu
WHERE pu.foo_id = act.id
AND pu.pathway_id = pw.id
ORDER BY lower(pw.element) ASC
) x
) AS pathway_array
FROM foo act
;

This returns:

id | pathway_array
----+----------------------------------------------------
1 | {"(\"{path,to,first}\")","(\"{path,to,second}\")"}
(1 row)

No, so far all good, *but*, I now want to retrieve this "pathway_array" column using JDBC and getArray(). My code looks like:

Array qArray = rs.getArray("pathway_array");
Object o = qArray.getArray();

throws Exception:
...
...
Caused by: org.postgresql.util.PSQLException: Method org.postgresql.jdbc4.Jdbc4Array.getArrayImpl(long,int,Map) is not yet implemented.
at org.postgresql.Driver.notImplemented(Driver.java:753)

So - does anyone know how to retrieve such arrays using JDBC?
Anyone knows if there's another way of retrieving the array of arrays without using the "array_agg trick" using a custom-type?

--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / CTO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Navanethan Muthusamy 2010-03-10 07:19:39 Clarification With Money data type
Previous Message Mark Kirkwood 2010-03-09 22:32:18 Re: org.postgresql.util.PSQLException: Protocol error. Session setup failed