postgres procedure list of values

Question

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;
$$

IN accepts a list of values, you are giving it a string. Why not use an array as an argument to the function instead? — GMB
– GMB, Commented Oct 1, 2020 at 23:01

GMB · Accepted Answer · 2020-10-01 23:03:26Z

2

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[]);

answered Oct 1, 2020 at 23:03

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Foo L Over a year ago

those are great solutions. how do I pass in a list? in my case, the ids are strings, ` source_id_list character varying[] := ('5f697ecb9ddf7744a7a1f935');` gives me error: ERROR: malformed array literal: "5f697ecb9ddf7744a7a1f935" DETAIL: Array value must start with "{" or dimension information. thanks

GMB Over a year ago

@FooL: ARRAY['5f697ecb9ddf7744a7a1f935']

Collectives™ on Stack Overflow

postgres procedure list of values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related