CODEWITHSUNDEEP
javapostgresqlspring-data-jpaspring-dataplpgsql
Denis Stephanov
Denis Stephanov

Reputation: 5231

Pass List<String> into postgres' function as parameter

I have spring data repository interface like this:

public interface MyEntityRepository extends 
        JpaRepository<MyEntity, Long> {

    @Query(nativeQuery = true, value = "select * from my_func(:myList)")
    Page<MyEntity> findBy(
            @NonNull @Param("myList") List<String> myList,
            @NonNull Pageable pageable);

}

Postgres' function I have defined like this (but I can change it if I did it wrong):

CREATE OR REPLACE FUNCTION my_func(variadic myList text[])
RETURNS SETOF myEntityTable AS $$
... some logic
select * from myEntityTable t where t.foo in (myList);

When I call this repository method I got this error:

ERROR: operator does not exist: character varying = text[]
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Where: PL/pgSQL function f_najdi_autorizaciu_na_spracovanie(text[]) line 28 at RETURN QUERY

Can you please tell me what type should I use in my postgres function? Thank you in advice.

EDIT: I cannot use native query above repository method and pass there list into IN clause because I have in DB function more logic, variables and so on ... it have to be DB function.

Upvotes: 5

Views: 16493

Answers (3)

questionto42
questionto42

Reputation: 9532

Not sure whether this add a lot of value, I just hope it helps.

The final format that the PostgreSQL function expects from a list as the input is of the following a pattern, using an array:

select my_func(array['item1','item2']::my_type[]);

See a full example on Database Administrators.SE.

In your case:

select my_func(array['item1','item2']::text[]);

and I found out in my own tests that

select my_func(array['item1','item2']);

should already be enough.

Then, the only aim is to get this format from your original java type. Thihs has been answered by the other question. This answer is just about showing the aim of it all.

Upvotes: 0

Cepr0
Cepr0

Reputation: 30309

I used the following workaround solution in the similar situation:

1) Created two helper functions:

-- Convert a variable number of text arguments to text array
-- Used to convert Java collection to the text array
--
create or replace function list_to_array(variadic _list text[]) returns text[] language sql as $$
select _list;
$$;

-- Convert the bytea argument to null.
-- Used to convert Java null to PostgreSQL null
--
create or replace function list_to_array(_list bytea) returns text[] language sql as $$
select null::text[];
$$;

2) Used any instead of in in the main function, for example:

create or replace function my_func(_params text[]) 
returns table (field1 text, field2 text) 
language sql as 
$$
select
  t.field1 as field1,
  t.field2 as field2,
from
  my_table t
where
  array_length(_params, 1) is null or t.foo = any(_params);
$$;

3) Then used them in a repository method, for example:

@NonNull
@Query(value = "select ... from my_func(list_to_array(?1))", nativeQuery = true)
List<MyProjection> getFromMyFunc(@Nullable Set<String> params, @NonNull Pageable page);

Upvotes: 6

Lukas Eder
Lukas Eder

Reputation: 220787

I'm afraid I don't know how to do this with Spring Data JPA, but with plain JDBC, you'd have to simply cast the bind variable to text[] and pass a String[] type instead of a list. For example:

try (PreparedStatement s = conn.prepareStatement("select * from my_func(?::text[])")) {
    s.setObject(1, myList.toArray(new String[0]));

    try (ResultSet rs = s.executeQuery()) {
        // ...
    }
}

The key message here is that the JDBC driver will expect an array, not a list.

Upvotes: 0

Related Questions