7

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).
  • 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. – Craig Ringer Nov 22 '14 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. – Erwin Brandstetter Nov 25 '14 at 1:02
5

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.

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.

  • 1
    thanks - even better! interesting that pg doesn't complain about surplus arguments in the 2nd example - if one filter is specified. – Richard Nov 25 '14 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. – Erwin Brandstetter Nov 25 '14 at 11:43
-2
RETURN QUERY EXECUTE SELECT
        TotalBookingAmount,
        TotalQuantity,
        TotalActualWeight,
        TotalChargedWeight,
        TotalLR,
        TotalDeliveryAmount,
        GrandTotalAmount,
        waybilltypeid,
        bookingmonth,
        bookingyear,
        sourcebranchid, 
        destinationbranchid 
                FROM '|| tableName || '
            where 
                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 Mar 9 at 10:10
  • 1
    this answer has nothing to do with the question. perhaps you've posted to the wrong page? – Richard Mar 11 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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