aggregates with complex type as state and init condition
От | Thomas Chille |
---|---|
Тема | aggregates with complex type as state and init condition |
Дата | |
Msg-id | 000901c48ec6$613d69a0$500a0a0a@spoon.de обсуждение исходный текст |
Ответ на | Re: moving an installation ("David Parker" <dparker@tazznetworks.com>) |
Ответы |
Re: aggregates with complex type as state and init condition
|
Список | 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!
В списке pgsql-general по дате отправления: