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