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

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

Hallo,

I am trying to get JDBC to pass an array of user_defined types to a
pl/pgsql function, via a preparedStatement and setObject()

function is defined thus:

CREATE FUNCTION mytypearrayfunc( long id_in, mytype[] typaarray_in) 
RETURNS SETOF RECORD AS $$
...
$$ LANGUAGE plpgsql ;

And I did define my own list_of_objects class

public class MyTypeList extends PGobject
{

...

public MyTypeList()
    {
      setType("_mytype");
    }
...
public getValue()
    {
      ...
    }
}

which returns representation in form 

ARRAY[
	ROW(7,'{5,6,7}','{4}')::mytype,
	ROW(2,'{2}','{3,4}')::mytype,
	ROW(1,'{1}','{}')::mytype
]

which is absolutely OK if I pass it to function in plpgsql but java gets
paranoid and demands me to start ARRAY[] argument with "{" :


Traceback (innermost last):
  File "<console>", line 1, in ?
  File "/home/hannu/work/M1/javatest/jythontest_func.py", line 62, in ?
   at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
   at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
   at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
   at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
   at java.lang.reflect.Method.invoke(libgcj.so.90)

org.postgresql.util.PSQLException: org.postgresql.util.PSQLException:
ERROR: array value must start with "{" or dimension information

Can anyone point me where to look for this check ?

grepping for the error message in driver source gives me nothing ,
probably it is some internationalised string that is not present in
source ?

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(# ] )
m3-# ;
 status | my_id | friends      | foes 
--------+-------+--------------+-----------
    200 |     7 | {5,6,7}      | {4}
    200 |     2 | {2}          | {3,4}
    200 |     1 | {1}          | {}
(3 rows)

and I get different error message when i do setType("somethingelse"); so
most of the PGObject machinery is working.

If really needed, I can prepare a full sample code ( a shell script, a
jython file, a java file and an sql file) but hopefully someone can tell
me the location of this check right away.


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