Re: rounding problems

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
>