Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-jdbc by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group