Re: [HACKERS] Implementing STDDEV and VARIANCE

Jeroen van Vianen <jeroen@design.nl> writes:
> aggname                
> aggowner
> aggtype
> aggtranstype           [ n, sx, sx2 ]
> agginitfunction        function that does ( n = 0, sx = 0.0, sx2 = 0.0 )
> aggtransfunction       function that does ( n = n + 1, sx = sx + x,
>                          sx2 = sx2 + x * x )
> aggfinalizefunction    function that returns (sx2 - (1/n) * sx * sx ) /
> n

Right, that's pretty much what I'm visualizing.  One minor detail: there
is not an "agginitfunction", there is an "agginitvalue".  So your
special datatype to hold n/sx/sx2 must have at least a typinput function
that can convert the text string held in pg_aggregate into the desired
internal form of the initial state.  (At least, that's how it's done
now.  Do you want to argue to change it?  As long as we're opening up
the AGG design for reconsideration, we could revisit that choice too.)

> Might it be better for me to wait for 7.1 before implementing stddev and
> variance?

Well, you will need to be pretty fast on the draw if you want to get it
into 7.0 release, since we will be freezing features for beta in a week.

But I see no reason that you couldn't implement STDDEV within the
existing framework; just ignore transfn2 and do it as above.  You might
have some problems with getting the desired response for zero or one
tuples, but there isn't any way to fix that within the current
framework :-(.  We have to do the function manager rewrite before you
can have control over when to return a NULL.  As long as you are willing
to live with that, you can have useful functionality now.
        regards, tom lane