Re: jdbc refuses to pass array argument using ARRAY[...] syntax

[Hannu Krosing <hannu(at)2ndQuadrant(dot)com>]

On Sat, 2009-02-07 at 11:28 -0500, Kris Jurka wrote:
> 
> On Sat, 7 Feb 2009, Hannu Krosing wrote:
> 
> > org.postgresql.util.PSQLException: org.postgresql.util.PSQLException:
> > ERROR: array value must start with "{" or dimension information
> 
> This is a server error message, not a JDBC driver message.
> 
> > and yes, the full query string returned from myPrepared
> > Statement.toString() is valid SQL and does produce desired results when
> > I paste it in psql, so the problem is very likely overly paranoid checks
> > in PG jdbc driver.
> >
> > m3=# select * from mytypearrayfunc( 1, ARRAY[
> > m3(# ROW(7,'{5,6,7}','{4}')::mytype,
> > m3(# ROW(2,'{2}','{3,4}')::mytype,
> > m3(# ROW(1,'{1}','{}')::mytype
> > m3(# ] )
> 
> Since you're using a prepared statement, this isn't the same thing.  The 
> equivalent is really:
> 
> PREPARE myplan ( mytype[] ) AS SELECT * FROM 
> mytypearraryfunc($1);
> 
> EXECUTE myplan ( 'ARRAY[..]'::mytype[] );
> 
> ARRAY is a grammar construct and cannot be part of the parameter.

weird, since this works too

m3=# prepare myplan(mytype[]) as SELECT * FROM mytypearraryfunc(1,$1);
PREPARE
m3=# execute myplan(ARRAY[                                  
m3(#                 ROW(10, '{1,2,3}','{7,8,9}')::mytype,
m3(#                 ROW(11, '{1}','{3,4,5,6,7}')::mytype,
m3(#                 ROW(10, '{}','{7}')::mytype
m3(#         ]);
 status | file_id | delete_heirs |  add_heirs  
--------+---------+--------------+-------------
    200 |      10 | {1,2,3}      | {7,8,9}
    200 |      11 | {1}          | {3,4,5,6,7}
    200 |      10 | {}           | {7}
(3 rows)
m3=# 



> Kris Jurka
-- 
------------------------------------------
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability 
   Services, Consulting and Training