Call database function that receives a list as parameter

Question

People like me do the following... involves database (Postgres SQL) and JPA, I want to create a database function that receives a list of ids as parameter and make a query cm enclosure WHERE IN (...). In JPA how do I call this function, and how do I pass this list of ids (Long) as parameter? I tried everything, I researched a lot but I was not successful.

Ex.:

CREATE OR REPLACE FUNCTION public.fnGetEmailUsuarios(ids int[])
RETURNS TABLE(email VARCHAR(40))
AS $BODY$
    BEGIN
    RETURN QUERY EXECUTE 'SELECT u.email FROM usuario u WHERE u.tipo IN ids';
    END;
$BODY$ LANGUAGE plpgsql;

Answer 1

You can do something like this:

public interface PersonRepository extends JpaRepository<Objeto, Long> {     
    @Query("SELECT fnGetEmailUsuarios(:lista)")
    public List<Objeto> find(@Param("lista") String[] lista);  
}

Answer 2

Consultation within the function public.fnGetEmailUsuarios( ids int[] ) has an error in its clause WHERE.

The operator IN() expects a list of scalar values and its function is passing to IN() an argument of the kind array.

You need operators capable of receiving type values array, such as SOME() or ANY().

Assuming you have something like:

CREATE TABLE public.usuario
(
    id INTEGER,
    email VARCHAR(40),
    tipo INTEGER
);


INSERT INTO public.usuario ( id, email, tipo ) VALUES (1,'[email protected]',    10);
INSERT INTO public.usuario ( id, email, tipo ) VALUES (2,'[email protected]',     20);
INSERT INTO public.usuario ( id, email, tipo ) VALUES (3,'[email protected]',    30);
INSERT INTO public.usuario ( id, email, tipo ) VALUES (4,'[email protected]',    40);
INSERT INTO public.usuario ( id, email, tipo ) VALUES (5,'[email protected]', 50);

Its function must be rewritten as follows in order to function correctly:

CREATE OR REPLACE FUNCTION public.fnGetEmailUsuarios(ids int[])
RETURNS TABLE(email VARCHAR(40))
AS $BODY$
    BEGIN
    RETURN QUERY SELECT u.email FROM public.usuario u WHERE u.tipo = ANY ( ids );
    END;
$BODY$ LANGUAGE plpgsql;

What would allow consultations of the type:

SELECT public.fnGetEmailUsuarios(  ARRAY[10,30,50] );

Answer 3

You can try it like this:

public interface UsuarioRepository extends JpaRepository<Usuario, Long> {   
    List<Usuario> findByTipo(List<Integer> tipos);
}

And in the calls:

usuarioRepository.findByTipo(Arrays.asList(0, 1));