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)
Best Answer
Use an array and the
ANY
operator:When calling the function you can use any array notation you like, I prefer
array[...]
: