Cumulative aggregate

Started by Paolo Saudinover 19 years ago2 messagesgeneral
Jump to latest
#1Paolo Saudin
paolo@ecometer.it

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);

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

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

Thank you very much,
Paolo Saudin
Italy

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Paolo Saudin (#1)
Re: Cumulative aggregate

"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