aggregates with complex type as state and init condition

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!

In response to

Responses

Browse pgsql-general by date

  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