Skip site navigation (1) Skip section navigation (2)

Re: aggregates with complex type as state and init condition

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 (view raw or flat)
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


pgsql-general by date

Next:From: Katsaros Kwn/nosDate: 2004-08-31 10:32:52
Subject: Storing a query plan to disc...
Previous:From: Martijn van OosterhoutDate: 2004-08-31 09:12:50
Subject: Re: DB failure?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group