Rounding in PGSQL
Hello all,
I have got one table with rounding values, table contains
prices and round types.
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?
I have got PHP and MySQL function, it returns correct result but I
need to count rounded price in db becase I have to compare this
rounded price with other records.
function roundValue($value){
$valueAll = (int) $value;
$tmpData = dbClass::fetch_assoc(dbClass::query(
'SELECT rv.rounding
FROM shop_rounding_values rv, shop_rounding r
WHERE rv.value_from <= \''.$valueAll.'\' AND (
rv.value_to > \''.$valueAll.'\' OR rv.value_to=0) AND
rv.rounding_id = r.id AND r.feshow = "y"'));
$rounding = (int) $tmpData['rounding'];
if($rounding == 0){
$value = round($value, 1);
}else if ($rounding == 1){
$value = round($value);
}else{
$value = round($value, substr_count($rounding, '0')*-1);
}
return $value;
}
Thanks for your replies.
--
Jiri Nemec
www.menea.cz - web solutions
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?
It's unclear whether you want to store the value in its original form
or in its rounded form.
If the latter, what you need is an 'on insert or update' trigger which
passes the value being inserted or updated through your rounding
function and stores the rounded value.
--
Mike Nolan
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 100Eg.:
price = 15.5758, rounded = 15.6
price = 1825.5540, rounded = 1826
price = 7125.123, rounded = 7130
price = 11825.5540, rounded = 11800Is 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