Re: Retrieving multidimentional array-column using JDBC

On Wednesday 10. March 2010 19.12.00 Kris Jurka wrote:
>
> On Wed, 10 Mar 2010, Andreas Joseph Krogh wrote:
>
> > 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:
>
> PG does support multidimensional arrays:
>
> kjurka=# select array[array['a','b'],array['c','d']];
> array
> ---------------
> {{a,b},{c,d}}
> (1 row)

Yes, I know it works using the array[...] construct, but it doesn't work (at least I don't know how to make it work) when the ARRAY()-construct is used to create an array of a sub-select returning rows of varchar-array.

If you see the example-query I sent, do you have any suggestion on how to rewrite it in order to make it return an array of varchar-arrays without using the custom-type text_array?

This query (using the ARRAY()-construct) fails:
SELECT act.id,
ARRAY(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
) as pathway_array
FROM foo act
;

ERROR: could not find array type for data type character varying[]

Hints?

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