From: | "Thomas Chille" <thomas(at)chille(dot)de> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: aggregates with complex type as state and init condition |
Date: | 2004-08-31 10:23:30 |
Message-ID: | 001b01c48f44$90048560$500a0a0a@spoon.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Katsaros Kwn/nos | 2004-08-31 10:32:52 | Storing a query plan to disc... |
Previous Message | Martijn van Oosterhout | 2004-08-31 09:12:50 | Re: DB failure? |