Re: Rounding in PGSQL

Jiri Nemec wrote:
> id price_from price_to rounding
> 1 0 1500 0.1
> 2 1500 5000 1
> 3 5000 15000 10
> 4 15000 0 100
>
> Eg.:
> price = 15.5758, rounded = 15.6
> price = 1825.5540, rounded = 1826
> price = 7125.123, rounded = 7130
> price = 11825.5540, rounded = 11800
>
> Is there some possibility how to write own PGSQL function which I pass in
> price, function selects correct value from "rounding" column and
> return rounded value?

Try something like this:

create or replace function ballpark(numeric) returns numeric as '
select case
when $1 <= 1500 then
round($1, 1)
when $1 <= 5000 then
round($1, 0)
when $1 <= 15000 then
round($1, -1)
else
round($1, -2)
end
' language sql;

select ballpark(15.5758),
ballpark(1825.5540),
ballpark(7125.123),
ballpark(11825.5540);
ballpark | ballpark | ballpark | ballpark
----------+----------+----------+----------
15.6 | 1826 | 7130 | 11830
(1 row)

HTH,

Joe