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

On Sat, 2009-02-07 at 23:57 +0200, Hannu Krosing wrote:
> 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=#

Oh, did not notice the '' around :

so the right way to do it would be

m3=# execute myplan(E'{"(10,\\"{1,2,3}\\",\\"{7,8,9}\\")","(11,{1},\
\\"{3,4,5,6,7}\\")","(10,{},{7})"}'::inheritance_change_type[]);
 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)

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