Обсуждение: 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