Thread: Re: aggregates with complex type as state and init condition

hi tom,

thanks for your tipps.

now it works for me. but i could not use a numeric[] array, because i found no way to assign the new
calculated values in the state-function (weighted_accum) via pgsql.

i follow the workaround i found on the list using point instead. but it s really dirty. Know anyone
how i can use numeric[] instead point to solve my problem?

I am using version 7.3.4 an can not switch to 8.0 (not now) with the lovly ARRAY-constructor.

Is the array-support in pl/java better then in pl/pgsql?

Here comes the working code:

CREATE OR REPLACE FUNCTION "public"."weighted_accum" (point, point) RETURNS point AS'
DECLARE
    _STATE ALIAS FOR $1;
    _IN ALIAS FOR $2;
    _OUT point;
    _WEIGHT numeric;
    _VALUE numeric;
BEGIN
    IF _IN[0] > 0 THEN
        _OUT := point(_STATE[0] + _IN[0], _STATE[1] + _IN[0] * _IN[1]);
    ELSE
        _OUT := point(_STATE[0], _STATE[1]);
    END IF;
    RETURN _OUT;
END
'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE OR REPLACE FUNCTION "public"."weighted_avg" (point) RETURNS numeric AS'
DECLARE
    _STATE ALIAS FOR $1;
    _OUT numeric;
    _WEIGHT numeric;
    _VALUE numeric;
BEGIN
    IF _STATE[0] > 0 THEN
        _OUT := _STATE[1] / _STATE[0];
    ELSE
        _OUT := _STATE[1];
    END IF;
    RETURN _OUT;
END
'LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

CREATE AGGREGATE "public"."wavg" (
 BASETYPE = "point",
 SFUNC = "public"."weighted_accum",
 STYPE = "point",
 FINALFUNC = "public"."weighted_avg",
 INITCOND = "(0,0)"
);

regards,
thomas