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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
