Re: I probably don't understand aggregates.

On 11.06.2014 23:43, Markus Neumann wrote:
> 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.
>>
>>
>
>
>
And now I even understood what you meant by Bottom-Post... -.-
Sorry again.