Thread: 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             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