postgres procedure list of values

I’m trying to pass in a list of values into a Postgres 11 procedure. The query has an IN condition. How do I pass in a list of values to the procedure & then how do I use it in the query?

create or replace procedure some_procedure(
  source_id_list character varying  <-- is this correct?
  )
language plpgsql
as $$
declare
  result_count int;
begin
select count(*)
into result_count
from table sd
where sd.id in source_id_list  <-- syntax error
;
RAISE NOTICE 'result: %', result_count;
end;
$$

Answer

IN expects a list of values, while you are giving it a string.

If you can change the datatype of the argument to the procedure, then it would be simpler to use an array:

create or replace procedure some_procedure(source_id_list int[])
language plpgsql
as $$
declare
    result_count int;
begin
    select count(*)
    into result_count
    from table sd
    where id = any(source_id_list);
    
    raise notice 'result: %', result_count;
end;
$$

If you can’t change the argument of the datatype, then another option uses string_to_array() to parse the string as an array:

select count(*)
into result_count
from table sd
where sd.id = any(string_to_array(source_id_list, ',')::int[]);