Re: Retrieving results from ARRAY and ROW - Mailing list pgsql-jdbc

On Tuesday 07 July 2009 02:14:00 am Andreas Joseph Krogh wrote:
> 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(),
whichresults 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
insteadof 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
forma path up to the top-most parent, which makes several sub-selects involving CTE unattractive. 

Replying to my self...
I found this post to return a dataset similar to what I'm having:
http://merlinmoncure.blogspot.com/2007/09/this-will-be-first-in-what-hopefully-be.html

id | array_accum
----+-----------------------------
1 | {"(abc,data)","(ghi,data)"}
2 | {"(def,data)","(jlk,data)"}

The question remains: How do I get arrays of composite types (ROW) in a type-safe way in my application using JDBC?

--
Andreas Joseph Krogh <andreak@officenet.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 |                                             |
------------------------+---------------------------------------------+