PostgresSQL dynamic execute with argument values in array

Either way, that's totally possible, given that all your parameters are of the same data type.

EXECUTE ... USING happily takes an array, which is treated as a single argument. Access elements with array subscripts.

create or replace function test_function(_filter1 text = null
                                       , _filter2 text = null
                                       , OUT retid int) as
$func$
declare
   _args text[] := ARRAY[_filter1, _filter2];
   _wher text[];
begin
   if _filter1 is not null then 
      _wher := _wher || 'parameter_name = $1[1]'; -- note array subscript
   end if;

   if _filter2 is not null then 
      _wher := _wher || 'parameter_name = $1[2]'; -- assign the result!
   end if;
  
   IF _args  IS NULL         -- check whether all params are NULL
      RAISE EXCEPTION 'At least one parameter required!';
   END IF;

   execute 'select id from mytable where ' -- cover case with all params NULL
         || array_to_string(_wher, ' or ')
         || ' ORDER BY id LIMIT 1';   -- For a single value (???)
   into  retid
   using _args;
end
$func$  language plpgsql;

This is just a proof of concept and needlessly complicated. It would be an interesting option for actual array input, for instance with a [VARIADIC function][2]. Example:

• How to do WHERE x IN (val1, val2,...) in plpgsql

For the case at hand, use instead:

CREATE OR REPLACE FUNCTION test_function(_filter1 text = null
                                       , _filter2 text = null)
  RETURNS SETOF int AS
$func$
DECLARE
   _wher text := concat_ws(' OR '
             , CASE WHEN _filter1 IS NOT NULL THEN 'parameter_name = $1' END
             , CASE WHEN _filter2 IS NOT NULL THEN 'parameter_name = $2' END);
BEGIN
   IF _wher = ''   -- check whether all params are NULL
      RAISE EXCEPTION 'At least one parameter required!';
   END IF;

   RETURN QUERY EXECUTE 'SELECT id FROM mytable WHERE ' || _wher
   USING  $1, $2;
   -- USING  _filter1 , filter2; -- alternatively use func param names
END
$func$  LANGUAGE plpgsql;

Explain

• List all values that can possibly be referenced in the dynamic query in the USING clause in their order of appearance. If not all of them will be referenced in the dynamic query, there's no harm in that. But we need to keep ordinal positions intact.

• Note in particular that $n inside the dynamic query references given values of the USING clause by ordinal number, while $n in the USING clause references function parameters. Same syntax, different scope!
  In my example $2 references $2 for simplicity. But one could reorder values in the USING clause in any fashion, so that (for instance) $2 in the dynamic query references $1 at the 2nd position in the USING clause, which references the 1st function parameter.

• This allows for any number of parameters with any (heterogeneous) data types.

• Returning a set of integer in this example(RETURNS SETOF int), which better fits the example - using RETURN QUERY EXECUTE accordingly.

• concat_ws() is particularly handy to assemble a list of OR'ed or AND'ed predicates conditionally.