Question 247035 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Java List<Integer> cast to int[] throws PSQLException: ERROR: syntax error

I need help with understanding and fixing the following issue.
I have created a postgresql function for retrieving all events in a specific regions based on regions like this:

CREATE OR REPLACE FUNCTION get_all_events_in_regions(regionIds int[])
RETURNS SETOF events AS $$
BEGIN
    RETURN QUERY
    SELECT  DISTINCT ON (e.id)
            e.id,
            e.event_type_id,
            e.vehicle_id,
            e.event_status_id,
            e.event_title,
            ...
    FROM events as e
    JOIN vehicle_region vr ON e.vehicle_id = vr.vehicle_id
    WHERE vr.region_id = ANY(regionIds);
END;
$$ LANGUAGE plpgsql;

And it works great when i call it form command line:

SELECT * FROM get_all_events_in_regions(ARRAY[1, 3,  6]);

Then i have a Spring Boot API that uses JPA for ORM and data persistence.
Inside a EnventRepository i created a native query method like this:

@Query(value = "SELECT e.* FROM get_all_events_in_regions(CAST(:regionIds as int[]) e", nativeQuery = true)
List<Event> getAllEventsInRegions(@Param("regionIds") List<Integer> regionIds, Pageable pageable);

But i get PSQLException: ERROR: syntax error at or near ","
regionIds is a normal ArrayList when i log it.

I also tried without CASTING into int[] but the error was:
PSQLException: ERROR: function get_all_events_in_regions(integer, integer, integer, integer, integer, integer, integer, integer, integer) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

And i don’t know what is going on or how to fix it.
Thanks for your help in advance

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks, I solved it.

    I had to convert it into a string that looks like this {6,12,9,4,10,8,11,5,7}.

    It appears that either Spring, JPA, or JDBC somehow, somewhere under the hood casts this string into int[] - ARRAY[6,12,9,4,10,8,11,5,7], which PostgreSQL understands.

    So I changed the parameter type from List to String and converted it like this

    private String convertListToPGString(List<Integer> integerList) {
    return "{" + integerList.stream()
            .map(Object::toString)
            .collect(Collectors.joining(",")) + "}";
}

  2. 0

    I’d guess you’d need to somehow register a converter from java.util.List -> java.sql.Array. The converter would do something like

    Array array = connection.createArrayOf("int", regionIds.toArray());

    Under the hood this java.sql.Array could then be set on the PreparedStatement

    Login or Signup to reply.
  3. 0

    Your error strongly implies that its binding in the List by expanding the List into its individual values.

    try

    get_all_events_in_regions(ARRAY[:regionIds]))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search