Re: I probably don't understand aggregates.
От | Markus Neumann |
---|---|
Тема | Re: I probably don't understand aggregates. |
Дата | |
Msg-id | 5398CD6A.308@math.uzh.ch обсуждение исходный текст |
Ответ на | I probably don't understand aggregates. (Markus Neumann <markus.neumann@math.uzh.ch>) |
Ответы |
Re: I probably don't understand aggregates.
(Markus Neumann <markus.neumann@math.uzh.ch>)
Re: I probably don't understand aggregates. (David G Johnston <david.g.johnston@gmail.com>) |
Список | 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. > >
В списке pgsql-novice по дате отправления:
Предыдущее
От: David G JohnstonДата:
Сообщение: Re: Need help with this Function. I'm getting an error