I have the following function in my postgres DB:

CREATE OR REPLACE FUNCTION schema1.ven_totais_nfes(data_inicial date, data_final date, setor_usuario text[])
 RETURNS TABLE(status character varying, quantidade bigint, valor numeric)
 LANGUAGE plpgsql
 SET search_path TO 'schema1', '$user', 'public'
AS $function$
 declare
    item record;
begin
    [...] 

executing a script like this:

select * from ven_totais_nfes('2022-12-01', '2022-12-31', array['aef4786e-7d99-4fdc-a20a-4da11c168eb2'])

returns me the desired results, but calling the function from a springboot backend , it brokes on the function call.

public List<NFeTotaisViewDto> findTotais(Date dataInicial, Date dataFinal, List<UUID>setorUsuario) {
        Query query = entityManager.createNativeQuery("select * from ven_totais_nfes(:dataInicial, :dataFinal, :setorUsuario)");
        query.setParameter("dataInicial", dataInicial);
        query.setParameter("dataFinal", dataFinal);
        query.setParameter("setorUsuario", setorUsuario);

ERROR: function ven_totais_nfes(unknown, unknown, uuid) does not exist Dica: No function matches the given name and argument types. You might need to add explicit type casts.

CodePudding user response：

The problem was the array type being passed to the function. I solved that using a for to iterate throw all the itens in the array and passing as a string to my text[] type in the function.

public List<NFeTotaisViewDto> buscarTotais(Date dataInicial, Date dataFinal, List<UUID> setores) {
        AtomicReference<String> stringSetores = new AtomicReference<>("{");

        for (int i = 0; i < setores.size(); i  ){
            stringSetores.set(stringSetores   setores.get(i).toString());
            if (i != setores.size() - 1){
                stringSetores.set(stringSetores   ",");
            } else {
                stringSetores.set(stringSetores   "}");
            }
        }
        return getRepository().findTotais(dataInicial, dataFinal, stringSetores.get());