Answer a question

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.

Answers

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);