Call a function with composite type as argument from native query in jpa

Question

I have created a postgres composite type and function as follows

CREATE TYPE  test AS (a int, b timestamp, c int);

CREATE FUNCTION some_updates(t test[])
    begin
    // iterate t and perform operation
    end

select some_updates(CAST(array[(488,'2019-7-01',342)] AS test[])

The above function and select to call the function works properly.

In spring jpa I would want to call function and pass values for "test[]" as params from native query. However it results in error.

@Query(value="select * from some_updates(:myinput)")
Myclass getDetails(List<test> myinput);

I have created a pojo class test as follows

class test
{
    int a;
    timestamp b;
    int c;

    //getters and setters
}

How can i possibly pass values to the postgres function?

Answer 1

Pass an array literal instead of an array constructor. The value can be passed as string literal, then it is coerced to the right type by Postgres:

SELECT some_updates('{"(488, 2019-07-01 00:00:00,342)"}');

Precondition: the function is not "overloaded" so that function type resolution is unambiguous with untyped input.

Related:

• How to pass custom type array to Postgres function
• Passing array of custom type to postgres function from node-pg and SQL injection
• Is there a way to disable function overloading in Postgres

If you have fewer than 100 array elements, a VARIADIC function would help to simplify passing arguments:

CREATE FUNCTION some_updates(VARIADIC t test[]) ...

Then pass row literals like:

SELECT some_updates('(488, 2019-07-01 00:00:00,342)'
                  , '(489, 2019-07-02 00:00:00,343)')

See:

• Return rows matching elements of input array in plpgsql function

If you have (many) more rows to pass, consider writing them to a (temporary) table with a plain INSERT and processing them from there.