How to cast a varchar into an int[] using 'in' operator in PostgreSQL

Question

I am having a hard time about this. I am trying to cast a varchar containing a list of numbers into an int array, in order to serve an in operator on a where clause. This is the last version of my code.

create or replace function is_product_in_categories (
    _product_id integer,
    _cat_list   varchar
)
returns boolean
as $$
declare
    _n integer;
begin
    _n = 0;

    select count(*) 
    into _n
    from category_products 
    where product_id = _product_id and category_id in (_cat_list::int[]);

  return _n > 0;
end;
$$ language plpgsql;


select is_product_in_categories(1, '1, 2, 3');

Error is

 SQL Error [42883]: ERROR: operator does not exist: integer = integer[]
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Where: PL/pgSQL function is_product_in_categories(integer,character varying) line 7 at SQL statement

I have tried several arguments, as '1, 2, 3', '(1, 2, 3)' or '[1, 2, 3]'. Also removing parenthesis near the in operator, etc.

Any idea?

Answer 1

The problem with the in operator is it doesn't admit an array as an argument. Instead it expects a simple list of scalars. See PostgreSQL documentation here https://www.postgresql.org/docs/9.0/functions-comparisons.html#AEN16964

To avoid this limitation the = any combination accepts an array as an argument. The code ends this way.

create or replace function is_product_in_categories (
  _product_id integer,
  _cat_list   varchar
)
returns boolean
as $$
declare
  _n integer;
begin
  _n = 0;

  select count(*) 
  into _n
  from of_category_products 
  where product_id = _product_id and category_id = any (_cat_list::int[]);

  return _n > 0;
end;
$$ language plpgsql;

select is_product_in_categories(1, '{1, 2, 3}')

Also, the syntax for literal arrays, using {} has been observed, following Bergi comment.