any plans to support more rounding methods in sql?

[raf <raf@xxxxxxx>]

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?

cheers,
raf


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general