hi again,
Some days ago I asked for help, nobody replied, and after trying to
do it in some way, I think aggregate function is the solution to my
problem, but I found difficult to understand how it works ...
What I want to do is accumulate the value for each row and add it to
the next:
a 100 100
b 50 150
c 25 175
My first approach was using a function/stored procedure, create a
cursor, loop through it, and insert the values in a temporary table,
that's why I was asking for those 'create table' before.
using a front-en application this is very easy, but I want to do it
directly in the server, and also it's a great exercice to learn more
about postgresql.
aggregate function
What I understand is:
I have to define the return-type of the aggregate function => float
The type of thing it aggregates => float (will be the value for each
row)
An initial value, probably 0
And a state-transition function to accumulate values (takes the
running total and the next value)
The optionally finalisation function I think I don't need it ...
How I can pack all this info in a aggregate function ?
In the manuals there is this example:
CREATE AGGREGATE avg (
sfunc = float8_accum,
basetype = float8,
stype = float8[],
finalfunc = float8_avg,
initcond = ’{0,0}’
);
but if I use real data with this aggregate, it doesn't work: (note
that I change avg to test, also in the create aggregate)
GlobalGest=# select test(saldo_deure,saldo_haver,saldo) from comptes
limit 5;
ERROR: function test(numeric, numeric, numeric) does not exist at
character 8
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
thanks in advance,
regards,
raimon fernandez
