How to define a parameter that will be used as an IN argument inside a function?

Question

I want to make a function that receives two parameters. The first one represents the salaries from a group of employees, and second one, the codes from a group of departments. Both, P_IN_SALARIES and P_IN_DEPARTMENTS_CODES parameters, will be used as arguments in an IN function of a query, just as demonstrated in the code below:

CREATE OR REPLACE FUNCTION public.get_employees_id(P_IN_SALARIES WHICH_TYPE_COMES_HERE, P_IN_DEPARTMENTS_CODES WHICH_TYPE_COMES_HERE)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
declare
    v_employees_ids text;
begin

    select STRING_AGG(employee.id || '', ',') into v_employees_ids
    from employee 
    inner join departament on department.id = employee.departament_id
    where employee.salary in (P_IN_SALARIES)
    and department.code in (P_IN_DEPARTMENTS_CODES);

    RETURN v_employees_ids;
END;
$function$

• What is the type of a IN parameter in a SQL statement?
• Is there a generic one that I might use in order to allow a kind of portability on an occasional database exchange (e.g. to Oracle)?
• How to call this function in a hibernate query?

Answer 1

In Oracle, you can use a collection data type:

CREATE TABLE number_list IS TABLE OF NUMBER;

Then you can use the MEMBER OF operator rather than IN:

CREATE FUNCTION public.get_employees_id(
  P_IN_SALARIES          IN number_list,
  P_IN_DEPARTMENTS_CODES IN number_list
) RETURNS VARCHAR2
IS
    v_employees_ids VARCHAR2(4000);
BEGIN
    SELECT LISTAGG( id, ',' ) WITHIN GROUP ( ORDER BY id )
    INTO   v_employees_ids
    FROM   employee e 
           inner join departament d
           on ( d.id = e.departament_id )
    WHERE  e.salary MEMBER OF P_IN_SALARIES
    AND    d.department.code MEMBER OF P_IN_DEPARTMENTS_CODES;

    RETURN v_employees_ids;
END;
/