Transfer initial condition as an argument to a custom aggregate

I want to create a function that takes an initial condition as an argument and uses a set of values ​​to calculate the final result. In my particular case (related to geometry processing in PostGIS), it is important that each member of the set is processed against the current (which may be the initial) state one at a time to save the result. (I have to deal with slit and whitespace issues and have had a really hard time doing this in any way other than one element at a time.) The processing I need to do is already defined as a function that takes two matching arguments (where the first could be the current state and the second could be a value from the set).

So, what I want is something similar to what you would expect to imply the following:

SELECT my_func('some initial condition', my_table.some_column) FROM my_table;

      

Aggregates seem natural to do this, but I can't figure out how to get the function to take on an initial state. An iterative approach in PL / pgSQL would be pretty simple:

CREATE FUNCTION my_func(initial sometype, values sometype[])
  -- Returns, language, etc.
  AS $$
  DECLARE
    current sometype := initial;
    v sometype;
  BEGIN
    FOREACH v IN ARRAY values LOOP
      current := SomeBinaryOperation(current, v);
    END LOOP;

    RETURN current;
  END
  $$

      

But this will require manually copying the values ​​to the array:

SELECT my_func('some initial condition', ARRAY_AGG(my_table.some_column)) FROM my_table;

      

You can create aggregates with multiple arguments, but the arguments following the first are used as additional arguments to the transition function. I do not see the slightest way to turn one of them into an initial state. (At least not without a remarkably hacky function that treats its third argument as an initial condition if the first argument is NULL or similar. And that's only if the aggregated argument can be a constant instead of a column.)

Is it best to use only PL / pgSQL's iterative approach, or is there a way to create an aggregate that takes its initial condition as an argument? Or is there something I didn't think about?

I am on PostgreSQL 9.3 at the moment, but upgrading might be an option if there is new material to help.

+3


source to share





All Articles