Pass List into postgres' function as parameter

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.

Arrays in Postgres are ***not*** the same as any other collection in any other language. They're... *different*. Could you explain what you're trying to accomplish with `my_func`? There may be a more elegant solution in conventional SQL. — Makoto, Mar 14 '19 at 18:42
@Makoto Hi, I edited my question. In my function I have some advanced logic with cursor, and so on. This passed parameter I need use just in final select in where condition in IN clause — Denis Stephanov, Mar 14 '19 at 18:48
So I believe that this is an XY problem - you're trying to find a set of records based on an IN query and there's an easier way to go about doing this than what you're proposing. I've found a dupe for this question; I encourage you to peruse it. If it doesn't fit, you can edit your question to explain why. — Makoto, Mar 14 '19 at 19:14
Possible duplicate of [Spring CrudRepository findByInventoryIds(List inventoryIdList) - equivalent to IN clause](https://stackoverflow.com/questions/18987292/spring-crudrepository-findbyinventoryidslistlong-inventoryidlist-equivalen) — Makoto, Mar 14 '19 at 19:15
@Makoto there is different problem like you post ... I need send list of strings into postgres function but I don't know what type I should use in that function. Your post is not solution for me because I cannot write this query directly into annotation without function .. there is more logic — Denis Stephanov, Mar 14 '19 at 19:39
Looks like the list is being passed successfully (you made it to line 28 of your function before encountering an error). I'm guessing the cause is `WHERE t.foo IN (myList)`; to check if `t.foo` is in your array, use `t.foo = ANY(myList)` instead. — Nick Barnes, Mar 14 '19 at 20:30
@NickBarnes now I am home from work and I tried it again with ANY but I got different error: ERROR: function my_func(character varying, character varying, character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. There is "character varying" same times as I have items in List — Denis Stephanov, Mar 14 '19 at 20:39
Does it work if you use `@NonNull @Param("myList") String[] myList`, instead? — Lukas Eder, Mar 14 '19 at 20:42
I suppose the reason I proposed that as a solution is because this is a very common thing to want to do with queries in JPA. I don't want to beat a dead horse per se, but what other logic do you have to account for such that you can't author the query like a "normal" SQL query? — Makoto, Mar 14 '19 at 20:42
@Makoto there are some cursors where I do another selects for calculation some values, and that values I'm using in final query where is also IN clause. I didn't write that whole function in my question because I can't avoid it. — Denis Stephanov, Mar 14 '19 at 20:45
@LukasEder I tried use String[] instead of List but I got this error: ERROR: function my_func(bytea) does not exist — Denis Stephanov, Mar 14 '19 at 20:47

Cepr0 · Accepted Answer · 2019-03-16T13:20:09.967

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

Lukas Eder · Answer 2 · 2019-03-14T20:45:00.987

0

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.

edited Mar 14 '19 at 20:45

answered Mar 14 '19 at 20:39

Lukas Eder

211,314
129
689
1,509

Thank you for answer but Jdbc template is our last option. We also considering about paging where spring's data jpa has nice api. – Denis Stephanov Mar 14 '19 at 21:01
@DenisStephanov Given that you're doing so much in that function already, why not move pagination into it as well? Seems wasteful to let the function return an entire result set only to paginate afterwards... – Lukas Eder Mar 14 '19 at 21:05
Because we have complicated frontend design. We receive pageable object with sort properties and so on. – Denis Stephanov Mar 14 '19 at 21:11
I also tried your solution but I got error function my_func(text[]) does not exist. I checked db and I am sure there is. Also I edit its parameter to text[] without variadic but doesn't help – Denis Stephanov Mar 14 '19 at 21:19
@DenisStephanov: You may need to qualify the function with the schema – Lukas Eder Mar 14 '19 at 21:20

score 0 · Answer 3 · answered Aug 26 '21 at 19:49

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.

Pass List into postgres' function as parameter

3 Answers3

Linked