Postgres function int array parameter error?

Question

I wrote a postgres function which takes an array of integers as an argument

create or replace function f_get_profiles(
            exp_array jsonb, 
            lng_array int[] 
        ) 

I am calling this function as :

SELECT * FROM 
f_get_profiles(
  '[{"role": 1, "experience": 2 }, {"role": 2, "experience": 3}]',
   array[1,2,3,4]::int[]
);

And in the function i want to use it as a regular integer array, I've tried it in 2 ways

format(' AND languages && %s', lng_array)

and

format(' AND languages && array[%s]', lng_array)

Both are giving me error :

error :

ERROR:  syntax error at or near "{"
LINE 3: ....role == 2 && @.experience >= 3)' AND languages && {1,2,3,4}

and

ERROR:  syntax error at or near "{"
LINE 3: ...= 2 && @.experience >= 3)' AND languages && array[{1,2,3,4}]

respectively....

Could anyone please help? Am i missing something here>?

---

full function

create or replace function f_get_profiles(
            exp_array jsonb, 
            lng_array int[], 
            _limit int=10, 
            _offset int=0
        ) 
    returns setof profiles 
    language plpgsql parallel SAFE stable strict as 
    $func$
        declare final_sql text;
        begin
            select into final_sql 
                E'SELECT * FROM profiles \nWHERE expertise @? ' ||
                string_agg(
                    quote_nullable(format('$[*] ? (@.role == %s && @.experience >= %s)', f->'role', f->'experience')) || '',
                    E'\nOR expertise @? '
                ) || format(' AND languages && %s', lng_array)
                  || E'\nLIMIT ' || _limit  || E'\nOFFSET ' || _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;
            end if;
        end
    $func$;

Answer 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.