By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,360 Members | 3,011 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,360 IT Pros & Developers. It's quick & easy.

Rounding in PGSQL

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
> 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2

P: n/a
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

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.