any plans to support more rounding methods in sql?

Hello

2012/1/25 raf <raf@raf.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

On 25 January 2012 05:41, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello

2012/1/25 raf <raf@raf.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:

Are you talking about always rounding down to the lower
smallest-possible-increment, rather than following standard rules for
rounding? That isn't such an esoteric use case - I believe that
financial regulations in some jurisdictions require just that when
calculating interest, for example.

If you require exactly 4 digits of precision, it's possible to use this syntax:

NUMERIC(precision, scale)

That isn't going to affect the rounding behaviour though.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

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

...

That should be IMMUTABLE not STABLE.

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.

...
Yes, but is that speed really noticeable, or is it just a rounding error (ha ha ha!)
compared to the total time of a query that uses these?

are there any plans to support any other rounding methods natively?

Probably not; it would be up to you to champion them here first. First
thing would be to demonstrate a clear use case. That may be hard, as
I have no heard of anyone else needing these, and rare features that
can be handled by extensions, user written functions, etc. are not
likely to be added to core. But who knows, there may be a valid use case.

First thing I would do is try a wrapper to a Python or Perl function,
mark it IMMUTABLE, and see just how slow it really is.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201201251316
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk8gRzEACgkQvJuQZxSWSsiFzgCfQdA/3sb8Y5eL6z59pR8EZQq7
ioYAoMjKXhrFlHjfSq/eSbvhKa1y+F+u
=swla
-----END PGP SIGNATURE-----