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.
Dave
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/creating-a-new-aggregate-function-tp5794414p5794448.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.