numeric rounding

[Gezeala 'Eyah' Bacu\361o II]

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_acquisitio n_date),
EXTRACT(year FROM fxam_acquisitio n_date),
fxam_dep_date,
fxam_salvage_va lue/100
INTO
depmonth,
depyear,
lastdepdate,
salvagevalue
FROM fixed_asset_mas ter 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=%'',d mon,dyear,depda te;

-- 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,depex pense;

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,depex pense;

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

Marie Gezeala M. Bacuño II
IS Department
Muramoto Audio-Visual Phils., Inc.
MEPZ1, Lapu-Lapu City, Cebu, Philippines 6015
The person with the ultimate cachinnation possesses, thereby, the optimal cachinnation.


---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

[Alvaro Herrera]

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)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Michael Meskes]

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: me****@jabber.o rg
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

"Gezeala 'Eyah' \"Bacuño\" II" <ge*******@yaho o.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.87500001000 00000
(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)::nu meric(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.8750000100 000000" 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Alvaro Herrera]

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(fl oat) returns float as '
regression'> begin
regression'> return round($1::numer ic, 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)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster