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.
--
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 | |
------------------------+---------------------------------------------+
