How to send list of integers into PostgreSQL function parameter?

Question

I have a function in Repository which calls a function like this:

@Query(value="select * from my_postgres_function(?1)",nativeQuery = true)
List<Map<String, String>> getScrutinyData(List<Integer> numbers;);

In .sql file, I have that function defined as

CREATE OR REPLACE FUNCTION public.my_postgres_function(numbers Integer[]);

The query in function is something like

select * from table t where t.id in numbers;

There seems to be an error and it doesn't work. Has anyone faced this before?

I have tried to send it as String and convert the value and put it, but it did work.

Does this answer your question? [How to properly call PostgreSQL functions (stored procedures) within Spring/Hibernate/JPA?](https://stackoverflow.com/questions/26122796/how-to-properly-call-postgresql-functions-stored-procedures-within-spring-hibe) — xerx593, Nov 29 '22 at 10:16
`@Procedure(value = "[public.]my_postgres_function") public List myPFunc(List numbers);` in your repo!(?) — xerx593, Nov 29 '22 at 10:19
I would send the list as a comma-separated string using `String.join`. The function becomes `my_postgres_function(numbers text)` and the function query - `where t.id = any(string_to_array(numbers, ','))` — Stefanov.sm, Nov 29 '22 at 11:20
Hey, welcome to SO! It would help us if you post the error that you're getting here. That will make it easier to answer your question. — Tiddo, Nov 29 '22 at 17:00

score 0 · Answer 1 · answered Dec 03 '22 at 14:29

This is right:

CREATE OR REPLACE FUNCTION public.my_postgres_function(numbers Integer[]);

inside the function you can use numbers like as this:

select * from table t where t.id in (select unnest(numbers))

In Java back-end side you can send your parameter using integer array.

1 Answers1