Re: I probably don't understand aggregates. - Mailing list pgsql-novice

Sorry for the PM David. (as I said thats my first mailing list...)
I checked for conflicts, there are none.

Thanks a lot for your time.

This is the smallest setup I could come up with, that reproduces the same
behaviour...

CREATE OR REPLACE FUNCTION myAggrStep(state INTEGER, sumup INTEGER)
RETURNS INTEGER AS
$$
   DECLARE outval INTEGER;
   BEGIN
     IF state IS NULL THEN
       outval := sumup;
     ELSE
       outval := state + sumup;
     END IF;
   RETURN outval;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION myAggrFinal(finalstate INTEGER)
RETURNS INTEGER AS
$$
   DECLARE outval INTEGER;
   BEGIN
     outval := 1/finalstate;
     RETURN outval;
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE myAggr(INTEGER)(
     SFUNC = myAggrStep,
     STYPE = INTEGER,
     FINALFUNC = myAggrFinal
);

CREATE OR REPLACE FUNCTION myWrapperFunc(x INTEGER)
RETURNS INTEGER AS
$$
   DECLARE outval INTEGER;
   BEGIN
     outval := myAggr(x);
     RETURN outval;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE test(x integer);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(1);

After all this:
SELECT myAggr(x) FROM test;

works.

SELECT myWrapperFunc(x) FROM test;

Division by zero!


Hope I didn't put any copy/paste errors...
On 11.06.2014 22:41, David G Johnston wrote:
> Markus Neumann wrote
>> I'm new to postgres, sql and mailing lists, so I think I'm in the right
>> place here.
>>
>> I have a pl/pgsql function, that calls a selfwritten aggregate function
>> (consisting of stepfunction and finalfunction) and afterwards processes
>> the results.
>>
>> The Problem:
>>
>> if I call
>>     SELECT myAggrFunction(x) FROM table;
>> I get the correct and expected output.
>>
>> but when myAggrFunction gets called inside the "wrapper"-function, the
>> finalfunction gets executed before all rows are processed and hence I
>> get a "Division by zero" error.
>>
>> E.g.
>>     SELECT myWrapperFunction(x) FROM table;
>>     ERROR: division by zero
>>
>>
>> myWrapperFunction would look something like:
>>
>>     BEGIN
>>       M := myAggrFunction(x);
>>       --do some more stuff
>>       RETURN M;
>>     END;
>>
>> I hope the description is somehow understandable.
>>
>> Any feedback will be highly appreciated.
>>
>> Markus
> Is there a reason you cannot provide a self-contained example?
>
> David J.
>
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/I-probably-don-t-understand-aggregates-tp5806879p5806885.html
> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>
>