Spring JPA - Custom function with array input

Mankind1023 0
Viewed 25

I have the following function script:

CREATE or REPLACE FUNCTION test1(_ids bigint[])
    RETURNS TABLE
            (
                businessId BIGINT,
                businessName VARCHAR
            )
    LANGUAGE plpgsql
AS
$$
BEGIN
    RETURN QUERY (
        SELECT id, name 
        FROM business b
        WHERE id = any(_ids)
    );
END;
$$

I can do the following and it works fine in normal psql commands:

SELECT * FROM test1(ARRAY [1,2,3])

How can I do this in Java? I tried passing an array of long, but it throws an sql grammar error.

repository:

  @Query(nativeQuery = true, value = "SELECT * FROM public.test1(?)")
  List<TestView> test1(long[] ids);

service:

long[] ids = {1L, 2L, 3L};
return businessRepository.test1(ids); // <-- does not work

If this is the incorrect way to pass an array / list of ids as an input parameter to a psql function, please advise.

jpa spring
1 Answers

Use a list rather than an array

@Query(nativeQuery = true, value = "SELECT * FROM public.test1(:ids)")
List<TestView> test1(@Param("ids") List<Long> ids);

Long[] ids = {1L, 2L, 3L};
return businessRepository.test1(Arrays.asList(ids));
johnnyutts0
Related