any plans to support more rounding methods in sql?

From: raf <raf(at)raf(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: any plans to support more rounding methods in sql?
Date: 2012-01-24 23:47:54
Message-ID: 20120124234754.GA12425@raf.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2012-01-25 00:10:32 Re: Best way to create unique primary keys across schemas?
Previous Message Merlin Moncure 2012-01-24 23:23:18 Re: Best way to create unique primary keys across schemas?