From: | "Thomas Chille" <thomas(at)chille(dot)de> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | aggregates with complex type as state and init condition |
Date: | 2004-08-30 19:20:14 |
Message-ID: | 000901c48ec6$613d69a0$500a0a0a@spoon.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
i am needing an aggregate-function wich calculates an weighted average about some rows.
for this reason i was creating a new complex type called 'wnumeric' and worked out all the
service-functions for the aggregate. everything is working fine, except that the first row will not
passed through the state-function (weighted_accum). instead the values of the first row are used
directly as init-state.
Now i tried to set the init-state to wnumeric(0, 0), but it wont work. The INITCOND-paramter only
accept string-literals like '0, 0', '(0.0,0.0)', but if i use the agrregate i get always this:
'ERROR: Cannot accept a constant of type RECORD'.
I think postgresql cast the INITCOND to the Type RECORD, what is wrong.
Can anyone help me to solve this problem?
Here comes the code:
CREATE TYPE "public"."wnumeric" AS (
"weight" REAL,
"value" NUMERIC
);
CREATE OR REPLACE FUNCTION "public"."wnumeric" (real, numeric) RETURNS "public"."wnumeric" AS'
DECLARE
_WEIGHT ALIAS FOR $1;
_VALUE ALIAS FOR $2;
_OUT "wnumeric"%rowtype;
BEGIN
SELECT INTO _OUT _WEIGHT, _VALUE;
RETURN _OUT;
END;
'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
CREATE OR REPLACE FUNCTION "public"."weighted_accum" ("public"."wnumeric", "public"."wnumeric")
RETURNS "public"."wnumeric" AS'
DECLARE
_STATE ALIAS FOR $1;
_IN ALIAS FOR $2;
_OUT wnumeric%rowtype;
BEGIN
IF _IN.weight > 0 THEN
_OUT.weight = _STATE.weight + _IN.weight;
_OUT.value = _STATE.value * _STATE.weight + _IN.value;
ELSE
_OUT.weight = _STATE.weight;
_OUT.value = _STATE.value;
END IF;
RETURN _OUT;
END
'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
CREATE OR REPLACE FUNCTION "public"."weighted_avg" ("public"."wnumeric") RETURNS numeric AS'
DECLARE
_STATE ALIAS FOR $1;
_OUT numeric;
BEGIN
IF _STATE.weight > 0 THEN
_OUT = _STATE.value / _STATE.weight;
ELSE
_OUT = _STATE.value;
END IF;
RETURN _OUT;
END
'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
CREATE AGGREGATE wavg(
BASETYPE=wnumeric,
SFUNC=weighted_accum,
STYPE=wnumeric,
FINALFUNC=weighted_avg
);
this produces the error:
CREATE AGGREGATE wavg(
BASETYPE=wnumeric,
SFUNC=weighted_accum,
STYPE=wnumeric,
FINALFUNC=weighted_avg
INITCOND='(1.0,1.0)'
);
regards
thomas!
From | Date | Subject | |
---|---|---|---|
Next Message | David Parker | 2004-08-30 20:52:32 | functionality like Oracle's "connect by" |
Previous Message | mallah | 2004-08-30 16:37:54 | Re: Generic/Common trigger |