how to pass an array parameter using jpa to a postgresql function?

Question

could someone tell me how to pass an array parameter to a postgresql function using EclipseLink (jpa 2.1)? I tried the below but get error:

Exception

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of [Ljava.lang.Integer;. Use setObject() with an explicit Types value to specify the type to use.

Integer[] propertyRowIndexArr = new Integer[]{1005, 1006, 1007};
StoredProcedureQuery query1 = myEntityManager.createStoredProcedureQuery("func_testArr");
query1.registerStoredProcedureParameter("itemdetailid", Integer[].class, ParameterMode.IN);
query1.setParameter("itemdetailid", propertyRowIndexArr);


CREATE OR REPLACE FUNCTION func_testArr(categoryid integer[])
  RETURNS setof category AS
$BODY$ 

DECLARE r category%rowtype;

BEGIN
        FOR r IN select * from category c where c.categoryid = any($1)
        LOOP
            RETURN NEXT r; 
        END LOOP;

END;

Thanks

[This](http://stackoverflow.com/questions/570393/postgres-integer-arrays-as-parameters) and [this](http://stackoverflow.com/questions/404941/how-can-i-pass-an-array-of-values-to-my-stored-procedure) might help you. — Ankur Singhal, Sep 04 '14 at 07:13
does this solves your problem, then i should post this as an answer for your acceptance — Ankur Singhal, Sep 05 '14 at 10:17
yes it did. It gave me an idea. Instead of passing an array to the function, I passed a string and delimiter the integers with a comma. Then, I used string_to_array inside the function to split the string into an array. Thanks, — fs2050, Sep 05 '14 at 12:59

score 0 · Answer 1 · answered Sep 18 '19 at 19:11

I did a workaround about this problem.

I needed to pass bidimensional string array (String[][]) as parameter into PostgreSQL function.

What I did was convert the full array to string, and then replace the square brackets by curly braces in the full string like this:

  query.setParameter(22, Arrays.deepToString(detalles_pedido).replace('[', '{').replace(']', '}'));

Then in my PostgreSQL function I changed the parameter type received to TEXT and I was able to cast the text to array using this instruction variableName::TEXT[ ][ ] inside the PostgreSQL function.

validacion := 0;    
FOREACH detalle SLICE 1 IN ARRAY set_detalles::text[][] LOOP    
    oidDetalle = uuid_generate_v4();
    SELECT "Oid" INTO productoID FROM public.cat_producto WHERE  descripcion = detalle[1];
    SELECT precio_unitario INTO precioUnitario FROM public.cat_producto WHERE  descripcion = detalle[1];

    SELECT count(dblink) INTO validacion FROM (SELECT dblink('dblink_trans', 'INSERT INTO public.detalle_pedido ("Oid", pedido_id , producto_id, cantidad, surtido, precio, "OptimisticLockField", "GCRecord") VALUES (''' || oidDetalle || ''',''' || Oid || ''',''' || productoID || ''', '|| detalle[2] || ', false,'|| precioUnitario || ', 0, null);')::TEXT) AS insercion;                    
END LOOP;

This really worked good for me.

how to pass an array parameter using jpa to a postgresql function?

1 Answers1