Обсуждение: numeric rounding

hey guys..need your help on this..

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 i want it to do is like this.

example:

721.875 = 721.88
721.865 = 721.87
721.765 = 721.77
721.775 = 721.78

here's my function which returns numeric(12,2):

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

DECLARE

  fxamid ALIAS FOR $1;
  -- life is in months
  life ALIAS FOR $2;
  acqamt ALIAS FOR $3;
  depmonth int4;
  depyear int4;
  depdate date;
  lastdepdate date;
  dyear int4;
  dmon int4;
  manth int4;
  manthlife int4;
  depexpense numeric(12,2);
 salvagevalue float4;
BEGIN
 
 SELECT
  EXTRACT(month FROM fxam_acquisition_date),
  EXTRACT(year FROM fxam_acquisition_date),
  fxam_dep_date,
  fxam_salvage_value/100
 INTO
  depmonth,
  depyear,
  lastdepdate,
  salvagevalue
 FROM fixed_asset_master WHERE fxam_id = fxamid;
  
 -- for Month of December
 IF  (depmonth = 12) THEN
  --Next year
  depyear := depyear + 1;
  --January the following year
  depmonth := 1;
 
 ELSE
  depmonth := depmonth + 1;
 
 END IF;
 
 -- first depreciation date of property based on acquisition date
 depdate := depmonth || ''/1/'' || depyear;

 -- RAISE NOTICE ''depdate = %'', depdate;

 -- get number of month and years from first depreciation date to last depreciation date 
 SELECT
  EXTRACT(month FROM AGE(lastdepdate,depdate::DATE)),
  EXTRACT(year FROM AGE(lastdepdate,depdate::DATE))
 INTO dmon,dyear;
 
 -- RAISE NOTICE ''dmon=%,dyear=%,depdate=%'',dmon,dyear,depdate;
 
 -- Number of months to depreciate
 manth := (dyear * 12) + dmon;
 -- Number of months of estimated life
 manthlife := life;

 -- Number of months to depreciate is greater than number of months of estimated life
 -- Only happens when property is encoded late and life is already consumed during first depreciation.. 
 IF ( dyear >= 0 AND manth > manthlife ) THEN
  
  -- Monthly depreciation expense Multiplied by number of month since Acquisition date

  depexpense := (acqamt - (acqamt * salvagevalue)) +  0.00000001;

  RAISE NOTICE ''manth = % > manthlife = %, depexpense=%'',manth, manthlife,depexpense;
  
 ELSE
 
  -- Monthly depreciation expense Multiplied by number of month since Acquisition date
  
  -- depexpense := ((acqamt - (acqamt * salvagevalue)) / life) * manth;
  depexpense := (((acqamt - (acqamt * salvagevalue)) / life) * manth) +  0.00000001;

  -- sample data :
  -- depexpense := (((2750.00 - (2750.00 * 0.1)) / 24 ) * 7) + 0.00000001 
  -- returns 721.87
  -- should return 721.88
  

  -- RAISE NOTICE ''manth = % < manthlife = %, depexpense=%'',manth, manthlife,depexpense;

 END IF;

RETURN depexpense;                                        

END;
'LANGUAGE 'plpgsql';

This function is up and running but my boss is such a great debugger.. ;)

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

I know that numeric data type automatically rounds off values but how come it's not returning the right values???

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)