aggregates with complex type as state and init condition - Mailing list 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!