Rounding in PGSQL - Mailing list pgsql-general

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