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? — xerx593
– xerx593, Commented Nov 29, 2022 at 10:16
@Procedure(value = "[public.]my_postgres_function") public List myPFunc(List<Integer> numbers); in your repo!(?) — xerx593
– xerx593, Commented Nov 29, 2022 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
– Stefanov.sm, Commented Nov 29, 2022 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
– Tiddo, Commented Nov 29, 2022 at 17:00

Ramin Faracov · Accepted Answer · 2022-12-03 14:29:51Z

0

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.

answered Dec 3, 2022 at 14:29

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1