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)
whenthe 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
arrayof 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@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 |                                             |
------------------------+---------------------------------------------+