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