Re: numeric rounding

"Gezeala 'Eyah' \"Bacuo\" II" <gezeala25(at)yahoo(dot)com> writes:
> -- sample data :
> -- depexpense := (((2750.00 - (2750.00 * 0.1)) / 24 ) * 7) + 0.00000001
> -- returns 721.87
> -- should return 721.88

I get

regression=# select (((2750.00 - (2750.00 * 0.1)) / 24 ) * 7) + 0.00000001 ;
?column?
----------------------
721.8750000100000000
(1 row)

I don't see any problem there. If you coerce the value to numeric(12,2)
you get the desired answer:

regression=# select ((((2750.00 - (2750.00 * 0.1)) / 24 ) * 7) + 0.00000001)::numeric(12,2);
numeric
---------
721.88
(1 row)

and the same even without the bogus add-on:

regression=# select ((((2750.00 - (2750.00 * 0.1)) / 24 ) * 7))::numeric(12,2);

numeric
---------
721.88
(1 row)

regression=#

I think what's probably happening is your function is returning
"721.8750000100000000" and something on the client side is simply
dropping digits beyond the ".87".

It may help to point out that although the system will syntactically
accept length limits on function arguments and results, those limits are
not enforced. That is, you wrote

CREATE OR REPLACE FUNCTION fn_adjust_accum_dep(int4,int4,numeric(12,2))
RETURNS numeric(12,2) AS ...

but this is really the same as

CREATE OR REPLACE FUNCTION fn_adjust_accum_dep(int4,int4,numeric)
RETURNS numeric AS ...

If you want the result rounded to 2 digits then you need to apply an
explicit coercion within the function. For example change
RETURN depexpense;
to
RETURN depexpense :: numeric(12,2);

regards, tom lane