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

Re: Retrieving multidimentional array-column using JDBC

From: Andreas Joseph Krogh <andreak(at)officenet(dot)no>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Retrieving multidimentional array-column using JDBC
Date: 2010-03-10 18:20:33
Message-ID: 201003101920.34508.andreak@officenet.no (view raw or flat)
Thread:
Lists: pgsql-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 |                                             |
------------------------+---------------------------------------------+

In response to

pgsql-jdbc by date

Next:From: Major ServicesDate: 2010-03-10 18:34:39
Subject: Re: org.postgresql.util.PSQLException: Protocol error. Session setup failed
Previous:From: Kris JurkaDate: 2010-03-10 18:12:00
Subject: Re: Retrieving multidimentional array-column using JDBC

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