Call database function that receives a list as parameter

Question

Call database function that receives a list as parameter

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

1

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

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;

sql postgresql jpa spring-boot

asked by anonymous 20.10.2018 / 15:21

3 answers

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);  
}

20.10.2018 / 18:47

0

You can try this:

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

And in calls:

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

22.10.2018 / 22:29

Script in Python via PHP Previous button disables when clicked on next

score 1 · Accepted Answer

The query inside the public.fnGetEmailUsuarios( ids int[] ) function has an error in its WHERE clause.

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

You need operators capable of receiving values of type 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);

Your function should be rewritten as follows in order to work 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 make queries of the type:

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