Re: any plans to support more rounding methods in sql?

Hello

2012/1/25 raf <raf(at)raf(dot)org>:
> hi,
>
> i just needed to round some numbers down to 4 decimal places but a quick search
> indicated that postgresql doesn't support all of the rounding methods so i had
> to write this dreadful function:
>
> create or replace function round_down_to_4_decimal_places(amount decimal(12,6))
> returns decimal(10,4) stable language sql as $$
>
>    select
>        case
>            when $1 >= 0 then
>                case when $1 - round($1, 4) < 0 then round($1, 4) - 0.0001 else round($1, 4) end
>            else
>                case when $1 - round($1, 4) > 0 then round($1, 4) + 0.0001 else round($1, 4) end
>        end
>
> $$;
>
> this is fine for my purposes but it's not generic to different numbers of decimal
> places and it's 26 times slower than the built-in round(v numeric, s int).
> strangely, a plpgsql version is much faster but it's still 11 times slower than
> a built-in version would be.
>
> python's decimal module supports the following rounding methods:
>
>  ROUND_UP        (round away from zero)
>  ROUND_DOWN      (round towards zero)
>  ROUND_CEILING   (round up)
>  ROUND_FLOOR     (round down)
>  ROUND_HALF_UP   (round 5 away from zero, rest to nearest)
>  ROUND_05UP      (round away from zero if last significant digit is 0 or 5, rest towards zero)
>  ROUND_HALF_DOWN (round 5 towards zero, rest to nearest)
>  ROUND_HALF_EVEN (round 5 to even, rest to nearest)
>
> are there any plans to support any other rounding methods natively?

numeric operations are not usual use case for relation databases. For
almost all users this complex set of functions should be contra
productive.

In PostgreSQL you can use a PLPythonu functionality or if you need it,
then you can write own fast implementation in C.

Regards

Pavel Stehule

>
> cheers,
> raf
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general