Re: I probably don't understand aggregates.

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.
>
>