Postgres function int array parameter error?

Solution 1:

Though possible, don't concatenate value parameter into the query string. Pass those as value.
Going out on a limb, your function might simply look like this:

CREATE OR REPLACE FUNCTION f_get_profiles(exp_array jsonb, lng_array int[])
   RETURNS SETOF tbl
   LANGUAGE sql AS 
$func$
SELECT *
FROM   tbl t
WHERE  ...  -- more predicates
AND    t.languages && lng_array
$func$;

If you actually need dynamic SQL in a PL/pgSQL function, pass the value with the USING clause:

CREATE OR REPLACE FUNCTION f_get_profiles(exp_array jsonb, lng_array int[])
   RETURNS SETOF tbl
   LANGUAGE plpgsql AS 
$func$
BEGIN
   RETURN QUERY EXECUTE format(
   $q$
   SELECT *
   FROM   tbl t
   WHERE  ...  -- more predicates
   AND    t.languages && $1
   $q$)
   USING  lng_array;
END;
$func$

Examples:

• Cannot use variable with EXECUTE command in plpgsql function
• INSERT with dynamic table name in trigger function

Applied to your function

CREATE OR REPLACE FUNCTION f_get_profiles(
            _exp_array jsonb,
            _lng_array int[],
            _limit int = 10,
            _offset int = 0)
  RETURNS void -- SETOF profiles
  LANGUAGE plpgsql PARALLEL SAFE STABLE STRICT AS
$func$
DECLARE
   final_sql text;
BEGIN
   SELECT INTO final_sql
          format(
$q$SELECT * FROM profiles 
WHERE  (%s)
AND    languages && $1  -- 👈
LIMIT  3
OFFSET 4$q$   
               , string_agg(format($s$jsonb_path_exists(expertise, '$[*] ? (@.role == $role && @.experience >= $experience)', %L)$s$, f), E'OR ')
               , _limit  
               , _offset)
   FROM jsonb_array_elements(_exp_array) AS f;
   
   IF final_sql IS NULL THEN
       RAISE EXCEPTION 'SQL statement is NULL. Should not occur!';
   ELSE
       RETURN QUERY EXECUTE final_sql USING _lng_array;    -- 👈
   END IF;
END
$func$;

Executes a statement of the form:

SELECT * FROM profiles 
WHERE  (jsonb_path_exists(expertise, '$[*] ? (@.role == $role && @.experience >= $experience)', '{"role": 1, "experience": 2}')
     OR jsonb_path_exists(expertise, '$[*] ? (@.role == $role && @.experience >= $experience)', '{"role": 2, "experience": 3}'))
AND    languages && $1
LIMIT  3
OFFSET 4

I replaced the operator @? with jsonb_path_exists(). This way, we can pass the JSON object with parameters to the function directly.

While being at it, I added missing parentheses around the OR'ed predicates. Remember: AND binds before OR.

Alternative syntax

Or we stick with @?, but with a single JSON path expression:

CREATE OR REPLACE FUNCTION pg_temp.f_get_profiles(
            _exp_array jsonb,
            _lng_array int[],
            _limit int = 10,
            _offset int = 0)
  RETURNS void -- SETOF profiles
  LANGUAGE plpgsql PARALLEL SAFE STABLE STRICT AS
$func$
DECLARE
   final_sql text;
BEGIN
   SELECT INTO final_sql
          format(
$q$SELECT * FROM profiles 
WHERE  expertise @? '$[*] ? (%s)'
AND    languages && $1   -- 👈
LIMIT  3
OFFSET 4$q$   
               , string_agg(format('@.role == %s && @.experience >= %s', (f->'role')::int, (f->'experience')::int), ' || ')
               , _limit  
               , _offset)
   FROM jsonb_array_elements(_exp_array) AS f;

   IF final_sql IS NULL THEN
       RAISE EXCEPTION 'SQL statement is NULL. Should not occur!';
   ELSE
      RETURN QUERY EXECUTE final_sql USING _lng_array;    -- 👈
   END IF;
END
$func$;

Executes a statement of the form:

SELECT * FROM profiles 
WHERE  expertise @? '$[*] ? (@.role == 1 && @.experience >= 2 || @.role == 2 && @.experience >= 3)'
AND    languages && $1
LIMIT  3
OFFSET 4

The cast to integer I slipped in here (f->'role')::int, (f->'experience')::int) has the sole purpose to defend against otherwise possible SQL injection. If you can rule out SQLi, you can strip the cast.