Re: creating a new aggregate function - Mailing list pgsql-sql

On Mon, 3 Mar 2014 13:12:01 -0800 (PST),
David Johnston <polobo@yahoo.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$
DECLARExyrows angle_vector;x_avg numeric;y_avg numeric;magnitude numeric;angle_avg numeric;

BEGINxyrows := 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