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
  stmt text;
  args varchar[];
  wher varchar[];
  retid integer;
  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;
$$ 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).
  • No, EXECUTE ... USING can't take an array of arguments, because PostgreSQL arrays don't support hetrogenous types. They must be an array of a single concrete type, and the SQL might not have the same type for each parameter. It'd be useless except for very narrow use cases. Instead, USING would have to be able to take an anonymous record, like you create from a ROW(...) constructor ... which is probably possible, but not currently implemented. Commented Nov 22, 2014 at 5:49
  • It's best explained with an example plus an actual explanation. Do you intend to apply all parameters to the same column parameter_name? (That could be largely simplified.) Or are you really thinking of different columns? Do you want to return a single value or a set of values? A bunch of OR'd predicates typically return multiple rows, which does not fit your RETURNS clause. Commented Nov 25, 2014 at 1:02

2 Answers 2


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
   _args text[] := ARRAY[_filter1, _filter2];
   _wher text[];
   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;

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:

For the case at hand, use instead:

CREATE OR REPLACE FUNCTION test_function(_filter1 text = null
                                       , _filter2 text = null)
  LANGUAGE plpgsql AS
   _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);
   IF _wher = ''   -- check whether all params are NULL
      RAISE EXCEPTION 'At least one parameter required!';
   END IF;

   USING  $1, $2;
   -- USING  _filter1 , filter2; -- alternatively use func param names

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.

  • 1
    thanks - even better! interesting that pg doesn't complain about surplus arguments in the 2nd example - if one filter is specified.
    – Richard
    Commented Nov 25, 2014 at 7:46
  • @Richard: Arguments provided in the USING clause can be referenced 0-n times. It's not exactly the same concept as *args in python, it's actually more flexible. Commented Nov 25, 2014 at 11:43
                FROM '|| tableName || '
                accountgroupid          = ' || accountGropIdCol     :: numeric || '
            AND sourcebranchid          = ' || SourceBranchIdCol    :: numeric || '
            AND bookingmonth            = ' || bookingMonthCol      :: numeric || '
            AND bookingyear             = ' || bookingYearCol       :: numeric || '
            AND destinationbranchid     =  any (string_to_array(' || DestinationBranchIdCol ||',' || ',' || ')' :: numeric[] ||')';
  • Welcome to Database Administrators! Please explain how your query solves the author's problem; answers without explanation generally aren't received well.
    – Glorfindel
    Commented Mar 9, 2019 at 10:10
  • 1
    this answer has nothing to do with the question. perhaps you've posted to the wrong page?
    – Richard
    Commented Mar 11, 2019 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.