JPA calling function in a native query is sending multiple parameters instead a UUID Collection-CodePudding

I created a PostgreSQL function to get some data from an array of UUID.

i.e:

create function journey_statistics(journey_ids uuid[])
returns TABLE(project_id uuid, project_name character varying,...)
language plpgsql

If I run the next sql statement it returns the expected data:

select * from journey_statistics(array['0f36c7a5-04eb-4329-8e93-a13625a4ffa6'::uuid, 'bc10ee72-7b7f-4bbd-a70a-75477b484d58'::uuid])

But then, when I implement it on Java and run it. I am getting the next error:

o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: function journey_statistics(uuid, uuid) does not exist

Hint: No function matches the given name and argument types. You might need to add explicit type casts.

This is the native query I am using to call it. And I have used same in other similar functions with no errors. So I can not understand what is the issue or what I am doing wrong.

@Query(value = "select cast(project_id as varchar(36)) as projectId, project_name as projectName, cast(project_leader as varchar(36)) as projectLeader"  
        " from journey_statistics(:uuids)", nativeQuery = true)
Collection<JourneyStatisticsView> getJourneyStatisticsById(Collection<UUID> uuids);

I have tried to cast data to an array but it looks it is transform to a record[] array. But more strange is if I pass a Collection of Strings and then I try to cast them I get

function journey_statistics(character varying, character varying) does not exist

Any help appreciated, thank you.

CodePudding user response：

I found a workaround to pass the Collection into the function.

It is not as sophisticated as I wanted. But at least it works.

Basically, I have created a new Repository to use the entity manager and create my own sql statement.

@Repository
public class JourneyStatisticsCustomRepositoryImpl implements JourneyStatisticCustomRepository {

@PersistenceContext
private EntityManager entityManager;

@Override
public List getJourneyStatisticsByIds(Collection<UUID> uuids) {
    final Collection<String> formattedUUID = uuids.stream().map(uuid -> "cast('"   uuid   "' as uuid)").collect(Collectors.toSet());
    final String joinedUUIDs = Strings.join(formattedUUID.iterator(), ',');
    return entityManager.createNativeQuery("select cast(project_id as varchar(36)) as projectId, ..."  
            "... from journey_statistics(array["   joinedUUIDs  "])", JourneyStatisticsView.class).getResultList();
}

This sends the proper collection to the function and returns the data as expected.

Hope this could help someone else with similar issues.