Function to return N significant digits of given number

Question

I have seen several questions on this topic, but the answers do not seem useful to me.

I must create a function in PostgreSQL 10.5 that returns a number with N significant digits. I have tried different solutions for this problem, however I have a problem with a particular case. Below the example, where nmNumber is the number of the input parameters and nmSf is the number of significant digits.

SELECT round(nmNumber * power(10, nmSf-1-floor(log(abs(nmNumber ))))) / power(10, nmSf-1-floor(log(abs(nmNumber)))) result1,
       round(nmNumber, cast(-floor(log(abs(nmNumber))) as integer)) result2,
       floor(nmNumber / (10 ^ floor(log(nmNumber) - nmSf + 1))) * (10 ^ floor(log(nmNumber) - nmSf + 1)) result3;

If nmNumber = 0.0801 and nmSf = 2 then:

result 1 = 0.08; result2 = 0.08; result 3 = 0.08

The three results are incorrect given that:

1. The zeros immediately after the decimal point are not significant digits.
2. All non-zero digits are significant.
3. The zeros after digits other than zero in a decimal are significant.

According to point 3, the correct result of the previous example is: 0.080 and not 0.08 and although mathematically it turns out to be the same, visually I must obtain this result. Some idea of ​​how I can solve my problem? I suppose that returning a VARCHAR in exchange for a NUMERIC is part of the solution to be proposed.

Some idea or I'm missing something. Thank you.

Answer 1

This would implement what you ask for:

CREATE OR REPLACE FUNCTION f_significant_nr(_nr numeric, _sf int, OUT _nr1 text) AS
$func$
DECLARE
   _sign    bool;    -- record negative sign
   _int     int;     -- length of integral part
   _nr_text text;    -- intermediate text state
BEGIN
   IF _sf < 1 THEN   
      RAISE EXCEPTION '_sf must be > 0!';
   ELSIF _nr IS NULL THEN
      RETURN;  -- returns NULL
   ELSIF _nr = 0 THEN
      _nr1 := '0'; RETURN;
   ELSIF _sf >= length(translate(_nr::text, '-.','')) THEN -- not enough digits, optional shortcut
      _nr1 := _nr; RETURN;
   ELSIF abs(_nr) < 1  THEN
      _nr1 := COALESCE(substring(_nr::text, '^.*?[1-9]\d{' || _sf-1 || '}'), _nr::text); RETURN;
   ELSIF _nr < 0 THEN  -- extract neg sign
      _sign := true;
      _nr := _nr * -1;
   END IF;

   _int := trunc(log(_nr))::int + 1;         -- <= 0 was excluded above!

   IF _sf < _int THEN                        -- fractional digits not touched
      _nr1 := rpad(left(_nr::text, _sf), _int, '0');
   ELSE
      _nr1 := trunc(_nr)::text;
      IF _sf > _int AND (_nr % 1) > 0 THEN   -- _sf > _int and we have significant fractional digits
         _nr_text := right((_nr % 1)::text, -1);   -- remainder: ".123"
         _nr1 := _nr1 || COALESCE(substring(_nr_text, '^.*?[1-9]\d{' || (_sf - _int - 1)::text || '}'), _nr_text);
      END IF;
   END IF;

   IF _sign THEN
      _nr1 := '-' || _nr1;
   END IF;  
END
$func$  LANGUAGE plpgsql;

db<>fiddle here - with test case.

Deals with everything you throw at it, incl NULL, 0, 0.000 or negative numbers.

But I have doubts about your point 3 as commented.

Answer 2

postgres knows how to do that (even point 3), you just need to ask it the right way.

create or replace function 
   sig_fig(num numeric,prec int) returns numeric 
   language sql as
$$
  select to_char($1, '9.'||repeat('9',$2-1)||'EEEE')::numeric
$$;

That creates a format string producing scientific notation with a 3 digit mantissa. Uses that that to get the number as text Then converts that back to numeric yielding a conventional number with that many significant figures.

demo:

 with v as ( values (3.14159),(1234567),(0.02),(1024),(42),(0.0098765),(-6666) ) select column1 ,sig_fig(column1,3) as "3sf" from v;
  column1  |   3sf   
-----------+---------
   3.14159 |    3.14
   1234567 | 1230000
      0.02 |  0.0200
      1024 |    1020
        42 |    42.0
 0.0098765 | 0.00988
     -6666 |   -6670
(7 rows)