Thread: Cumulative aggregate
Hi all, I want to write an aggregate to sum the values for rain precipitations. I found a working example with integer values, but I cannot find a way to to the same with float ones. Here is what I did :
-- table test
create table mytest (fld1 int4, fld2 float4);
insert into mytest values (1, 1.23);
insert into mytest values (6, 5.38);
insert into mytest values (2, 0.12);
create table mytest (fld1 int4, fld2 float4);
insert into mytest values (1, 1.23);
insert into mytest values (6, 5.38);
insert into mytest values (2, 0.12);
-- function for integer (OK)
create function myint4_sum(int4,int4) returns int4
as 'select int4_sum($1,$2)::int4;'
language SQL;
-- aggregate
create aggregate myagg1_sum (basetype = int4, sfunc = myint4_sum, stype = int4);
-- result
select myagg1_sum(fld1) from mytest; --> give 9
create function myint4_sum(int4,int4) returns int4
as 'select int4_sum($1,$2)::int4;'
language SQL;
-- aggregate
create aggregate myagg1_sum (basetype = int4, sfunc = myint4_sum, stype = int4);
-- result
select myagg1_sum(fld1) from mytest; --> give 9
-- function for float (NOT WORKING)
create function myfloat4_sum(float4,float4) returns float4
as 'select float4pl($1,$2)::float4;'
language SQL;
-- aggregate
create aggregate myagg2_sum (basetype = float4, sfunc = myfloat4_sum, stype = float4);
-- result
select myagg2_sum(fld2) from mytest; --> NOTHING ??? INSTEAD OF 6.73
create function myfloat4_sum(float4,float4) returns float4
as 'select float4pl($1,$2)::float4;'
language SQL;
-- aggregate
create aggregate myagg2_sum (basetype = float4, sfunc = myfloat4_sum, stype = float4);
-- result
select myagg2_sum(fld2) from mytest; --> NOTHING ??? INSTEAD OF 6.73
What am I doing wrong ??
Thank you very much,
Paolo Saudin
Italy
"Paolo Saudin" <paolo@ecometer.it> writes:
> -- function for float (NOT WORKING)
> create function myfloat4_sum(float4,float4) returns float4
> as 'select float4pl($1,$2)::float4;'
> language SQL;
> -- aggregate
> create aggregate myagg2_sum (basetype = float4, sfunc = myfloat4_sum, stype
> = float4);
> -- result
> select myagg2_sum(fld2) from mytest; --> NOTHING ??? INSTEAD OF 6.73
> What am I doing wrong ??
(1) not marking the transition function as strict --- this affects the
behavior of the aggregate;
(2) not providing an initial condition.
regards, tom lane