Re: creating a new aggregate function

On Mon, 3 Mar 2014 13:12:01 -0800 (PST),
David Johnston <polobo(at)yahoo(dot)com> wrote:

> Sebastian P. Luque wrote
>> If I wanted to create an aggregate that also returns an angle_vectors
>> data type (with the average x and y components), I would need to
>> write a state transition function (sfunc for 'CREATE AGGREGATE') that
>> essentially sums every row and keeps track of the count of elements.
>> In turn, this requires defining a new data type for the output of
>> this state transition function, and finally write the final function
>> (ffunc) that takes this output and divides the sum of each component
>> (x, y) and divides it by the number of rows processed. This seems
>> very complicated, and it would help to look at how avg (for instance)
>> was implemented. I could not find examples in the documentation
>> showing how state transition and final functions are designed. Any
>> tips?

> "avg" is defined in 'C' so not sure you'd find it of help...

> It may be easier, and sufficient, to use "array_agg" to build of an
> array of some kind and then process the array since it sounds like you
> cannot easily define a state-transition function that does what it
> says, transitions from one "minimal" state to another "minimal" state.
> For instance, the average function maintains a running count and a sum
> of all inputs so that no matter how many inputs are encountered at any
> point in the processing the only in-memory data are the last count/sum
> pair and the current value to be added to the sum (while incrementing
> the count). If your algorithm does not facilitate this kind of
> transition function logic then whether you incorporate the array into
> your own custom aggregate or use the native "array_agg" facility
> probably makes little difference.

> Mostly speaking from theory here so you may wish to take this with a
> grain of sand and maybe waits for others more experienced to chime in.
> Either way hopefully it helps at least somewhat.

Thanks for that suggestion. It seemed as if array_agg would allow me to
define a new aggregate for avg as follows:

CREATE AGGREGATE avg (angle_vector)
(
sfunc=array_agg,
stype=anyarray,
finalfunc=angle_vector_avg
);

where angle_vector is the composite type as defined in my previous
email, and angle_vector_avg is a function taking anyarray, which would
use unnest() to allow access to the x,y components and carry out the
computations:

---<--------------------cut here---------------start------------------->---
CREATE OR REPLACE FUNCTION angle_vector_avg(angle_vector_arr anyarray)
RETURNS record AS
$BODY$
DECLARE
xyrows angle_vector;
x_avg numeric;
y_avg numeric;
magnitude numeric;
angle_avg numeric;

BEGIN
xyrows := unnest(angle_vector_arr);
x_avg := avg(xyrows.x);
y_avg := avg(xyrows.y);
magnitude := sqrt((x_avg ^ 2.0) + (y_avg ^ 2.0));
angle_avg := degrees(atan2(x_avg, y_avg));
IF (angle_avg < 0.0) THEN
angle_avg := angle_avg + 360;
END IF;
RETURN (angle_avg, magnitude);
END
$BODY$
LANGUAGE plpgsql STABLE
COST 100;
---<--------------------cut here---------------end--------------------->---

Unfortunately, 'CREATE AGGREGATE' in this case returns:

ERROR: cannot determine transition data type
DETAIL: An aggregate using a polymorphic transition type must have at least one polymorphic argument.

********** Error **********

ERROR: cannot determine transition data type
SQL state: 42P13
Detail: An aggregate using a polymorphic transition type must have at least one polymorphic argument.

--
Seb