Writing Functions - PostgreSQL: Up and Running - page 150

(1/n) ). It has various uses in finance, economics, and statistics. A geometric mean sub‐
stitutes for the more common arithmetic mean when the numbers range across vastly
different scales. A more suitable computational formula uses logarithms to transform
a multiplicative process to an additive one ( EXP(SUM(LN(x))/n) ). We'll be using this
method in our example.
For our geometric mean aggregate, we'll use two subfunctions: a state transition function
to sum the logs (see Example 8-5 ) and a final function to exponentiate the logs. We'll
also specify an initial condition of zero when we put everything together.
Example 8-5. Geometric mean aggregate: state function
CREATE OR REPLACE FUNCTION geom_mean_state ( prev numeric [ 2 ], next numeric )
RETURNS numeric [ 2 ] AS
$$
SELECT
CASE
WHEN $ 2 IS NULL OR $ 2 = 0 THEN $ 1
ELSE ARRAY [ COALESCE ( $ 1 [ 1 ], 0 ) + ln ( $ 2 ), $ 1 [ 2 ] + 1 ]
END ;
$$
LANGUAGE sql IMMUTABLE ;
Our state transition function takes two inputs: the previous state passed in as an array
with two elements, and the next addend in the summation. If the next argument eval‐
uates to NULL or zero, the state function returns the prior state. Otherwise, it returns a
new array in which the first element is the sum of the logs and the second element is
the running count.
We also need a final function, shown in Example 8-6 , that divides the sum from the state
transition by the count.
Example 8-6. Geometric mean aggregate: final function
CREATE OR REPLACE FUNCTION geom_mean_final ( numeric [ 2 ])
RETURNS numeric AS
$$
SELECT CASE WHEN $ 1 [ 2 ] > 0 THEN exp ( $ 1 [ 1 ] / $ 1 [ 2 ]) ELSE 0 END ;
$$
LANGUAGE sql IMMUTABLE ;
Now we stitch all the subfunctions together in our aggregate definition, as shown in
Example 8-7 . (Note that our aggregate has an initial condition that is the same data type
as what is returned by our state function.)
Example 8-7. Geometric mean aggregate: assembling the pieces
CREATE AGGREGATE geom_mean ( numeric ) (
SFUNC = geom_mean_state ,
STYPE = numeric [],
FINALFUNC = geom_mean_final ,