numeric rounding

On Mon, Sep 29, 2003 at 12:47:05AM -0700, Gezeala 'Eyah' Bacu�o II wrote:

my problem is my function just won't round some numbers properly..
what i want it to do is like this.

example:

721.875 = 721.88

a=> select round(721.875, 2);
round
--------
721.88
(1 row)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un gui�n que no toque nada para no causar da�os." (Jakob Nielsen)

On Mon, Sep 29, 2003 at 12:47:05AM -0700, Gezeala 'Eyah' BacuXo II wrote:

You can see I already added a value of 0.00000001 to the computation but I also get the same results.. :(

Why 0.00000001? If you want to round to the second digit by cutting the
number you have to add 0.005.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

"Gezeala 'Eyah' \"Bacu�o\" II" <gezeala25@yahoo.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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, 29 Sep 2003 00:47:05 -0700 (PDT), Gezeala 'Eyah' \"Bacu±o\" II wrote:

i have a plpgsql function where in i compute numeric values for my php scripts..
my problem is my function just won't round some numbers properly.

What do you mean by "properly?" How, exactly, do you want the numbers rounded? What
about negative numbers?
There is a round() function that offers various options. I do not know how it handles
negative numbers, though.
For money numbers I found this to be a reasonable method:

sign = (0.0 <= N) ? 1 : -1;
tmp = abs(N);
tmp = round((tmp + 0.005), 2);
N = tmp * sign;

- --
jimoe at sohnen-moe dot com
pgp/gpg public key: http://www.keyserver.net/en/
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 5.0 OS/2 for non-commercial use
Comment: PGP 5.0 for OS/2
Charset: cp850

wj8DBQE/eHV9sxxMki0foKoRAnitAJ4yd1LG4eBlNpsQL3tFLqera9JRQACg3rKX
LnDwRQkyga6uxBzeEjEX+qE=
=LIzc
-----END PGP SIGNATURE-----

On Tue, Sep 30, 2003 at 06:13:39PM -0700, Gezeala 'Eyah' Bacu�o II wrote:

sorry man, but round function can't be used/doesn't work inside a
plpgsql function..

Huh?

regression=> create or replace function try_to_round(float) returns float as '
regression'> begin
regression'> return round($1::numeric, 2);
regression'> end;
regression'> ' language plpgsql;
CREATE FUNCTION
regression=> select try_to_round(12.1231231);
try_to_round
--------------
12.12
(1 row)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No hay cielo posible sin hundir nuestras ra�ces
en la profundidad de la tierra" (Malucha Pinto)