Implementing rounding rule in plpgsql
Due to application requirements, I need to implement a rounding function
that is independant of the baked-in rounding functionality. I'd prefer to do
it in plpgsql for maximum portability.
To do this, I'll need to sequentially walk through the digits of an
arbritarily long floating-point number. I can't think of a good way to do
this.
I would greatly appreciate pointers to similar example code or discussion of
the method for doing this..
On Sun, Nov 20, 2005 at 02:01:02AM -0500, jeff sacksteder wrote:
Due to application requirements, I need to implement a rounding function
that is independant of the baked-in rounding functionality. I'd prefer to do
it in plpgsql for maximum portability.
To do this, I'll need to sequentially walk through the digits of an
arbritarily long floating-point number. I can't think of a good way to do
this.
This wouldn't be a homework assignment, would it? Anyway, maybe
this will give you some ideas:
CREATE FUNCTION myround(num double precision) RETURNS double precision AS $$
DECLARE
textval text := num;
i integer;
BEGIN
FOR i IN 1 .. length(textval) LOOP
RAISE INFO '[%] = %', i, substr(textval, i, 1);
END LOOP;
RETURN num;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
test=> SELECT myround(1.23456789);
INFO: [1] = 1
INFO: [2] = .
INFO: [3] = 2
INFO: [4] = 3
INFO: [5] = 4
INFO: [6] = 5
INFO: [7] = 6
INFO: [8] = 7
INFO: [9] = 8
INFO: [10] = 9
myround
------------
1.23456789
(1 row)
--
Michael Fuhr
On Sun, Nov 20, 2005 at 02:24:20AM -0700, Michael Fuhr wrote:
On Sun, Nov 20, 2005 at 02:01:02AM -0500, jeff sacksteder wrote:
Due to application requirements, I need to implement a rounding function
that is independant of the baked-in rounding functionality. I'd prefer to do
it in plpgsql for maximum portability.
To do this, I'll need to sequentially walk through the digits of an
arbritarily long floating-point number. I can't think of a good way to do
this.This wouldn't be a homework assignment, would it? Anyway, maybe
this will give you some ideas:
It occurred to me after I posted this that there might be a numerical
method for walking through the digits. Successive multiplication by
10 has problems due to the inaccuracies of floating-point arithmetic,
although using a numeric type instead of double precision might solve
that problem. Maybe somebody more familiar with numerical methods
than I will suggest something.
--
Michael Fuhr