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

Retrieving results from ARRAY and ROW

From: Andreas Joseph Krogh <andreak(at)officenet(dot)no>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Retrieving results from ARRAY and ROW
Date: 2009-07-07 00:14:00
Message-ID: 200907070214.01001.andreak@officenet.no (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi all!

(Using pg-8.4 with postgresql-8.4-701.jdbc4.jar)
I have the following (simplified for example) schema:

CREATE TABLE person(id INTEGER, name VARCHAR);
INSERT INTO person(id, name) 
VALUES (1,'James'),(2, 'Jack');
CREATE TABLE person_dep(person_id INTEGER, dep VARCHAR);
INSERT INTO person_dep(person_id, dep) 
VALUES (1,'Dep1'),(1,'Dep2');

SELECT p.id, p.name
     , ARRAY(SELECT ROW(dep.person_id, dep.dep)
               FROM person_dep dep
              WHERE dep.person_id = p.id) AS my_array
  FROM person p
 WHERE p.id IN (1,2);

 id | name  |        my_array
----+-------+-------------------------
  1 | James | {"(1,Dep1)","(1,Dep2)"}
  2 | Jack  | {}
(2 rows)

The problem is that I'm not able to retrieve the results from "my_array" using rs.getArray("my_array").getArray(), which results in:
org.postgresql.util.PSQLException: Method org.postgresql.jdbc4.Jdbc4Array.getArrayImpl(long,int,Map) is not yet implemented.

Does someone know any better way to write this query which makes it work with JDBC? I know I can write 2 sub-selects instead of one and have each column return and array of integer[] and varchar[], but I'm trying to avoid having more than *one* sub-select.

My actual query involves a "WITH RECURSIVE" sub-select which retrieves <id,name> pairs for the parent of each node to form a path up to the top-most parent, which makes several sub-selects involving CTE unattractive.

-- 
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: Andreas Joseph KroghDate: 2009-07-07 01:04:04
Subject: Re: Retrieving results from ARRAY and ROW
Previous:From: Greg StarkDate: 2009-07-06 18:27:55
Subject: Re: PostgreSQL_db_change_notification

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