From: | Justin <justin(at)emproshunts(dot)com> |
---|---|
To: | Andy Anderson <aanderson(at)amherst(dot)edu>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: rounding problems |
Date: | 2008-05-13 17:01:11 |
Message-ID: | 4829C957.2080203@emproshunts.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks to Andy's C code here is the pl/pgSQL function which does the
same thing.
I added a tiny bit so instead of returning a numeric value with 20
trailing zeros it returns a value with the desired precision.
-----------------Begin Code ---------------------
create or replace function roundup(pToRound numeric, pPrecision
integer) returns numeric as
$Body$
declare
factor numeric ;
poweredup numeric;
trunced numeric;
fraction numeric ;
Begin
factor := power(10.0, pPrecision) ;
poweredup := pToRound * factor ;
trunced := trunc(poweredup);
fraction := poweredup - trunced;
if (fraction >= 0.5) then
return trunc(((trunced + 1)/factor), pPrecision);
end if ;
if (fraction <= -0.5) then
return trunc(((trunced - 1)/factor), pPrecision);
end if ;
return trunc((trunced/factor), pPrecision);
END;
$Body$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION roundup(pToRound numeric, pPrecision integer) owner to
postgres ;
---------------End Code -----------------------
>
>
> function roundup(n, i)
> {
> factor = power(10.0, i);
> nd = n * factor;
> ni = trunc(nd);
> fraction = nd - ni;
> if (fraction >= 0.5)
> return (ni + 1)/factor;
> if (fraction <= -0.5)
> return (ni - 1)/factor;
> return ni/factor;
> }
>
> Apologies for using C and warnings that I haven't thoroughly tested this.
>
> P.S. You could also write a round-even function for Excel and get them
> to use it on their next printout! :-)
>
> -- Andy
>
From | Date | Subject | |
---|---|---|---|
Next Message | Justin | 2008-05-13 17:29:07 | Re: rounding problems |
Previous Message | salman | 2008-05-13 17:00:58 | Re: how can i get initdb |