PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres:

Best explained using a contrived example:

create or replace function test_function(filter_param1 varchar default null
                                       , filter_param2 varchar default null)
  returns integer as
$ 
declare
  stmt text;
  args varchar[];
  wher varchar[];
  retid integer;
begin
  if filter_param1 is not null then 
    array_append(args, filter_param1);
    array_append(wher, 'parameter_name = $1');
  end if;
  if filter_param2 is not null then 
    array_append(args, filter_param2);
    array_append(wher, 'parameter_name = $2');
  end if;

  stmt := 'select id from mytable where ' || array_to_string(wher, ' or ');
  execute stmt into retid using args;

  return retid;
end;
$ language plpgsql;

In Python there is *args - perhaps PostgreSQL has a similar mechanism?

EDIT for Erwin Brandstetter questions:

• All filter parameters will be applied to different columns, but should be AND'ed.

• Returning setof makes much more sense here.

• All parameters can be of the same column type (ie. varchar).

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. Your original function adapted:

create or replace function test_function(_filter1 text = null
                                       , _filter2 text = null
                                       , OUT retid int)
  language plpgsql 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$;

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. 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
  LANGUAGE plpgsql 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$;

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, I kept the order of parameters in the USING clause in sync with function parameters, for simplicity. But we could list values in the USING clause in any order so that, for instance, $2 in the dynamic query points to $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.

StackExchange Database Administrators Q#83278, answer score: 9