Add functions min_scale(numeric) and trim_scale(numeric).

These allow better control of trailing zeroes in numeric values.

Pavel Stehule, based on an old proposal of Marko Tiikkaja's;
review by Karl Pinc

Discussion: https://postgr.es/m/CAFj8pRDjs-navGASeF0Wk74N36YGFJ+v=Ok9_knRa7vDc-qugg@mail.gmail.com

5 files changed, 255 insertions, 1 deletions

diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 14054272c89..76a597e56fa 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -3179,6 +3179,97 @@ numeric_scale(PG_FUNCTION_ARGS)
 	PG_RETURN_INT32(NUMERIC_DSCALE(num));
 }
 
+/*
+ * Calculate minimum scale for value.
+ */
+static int
+get_min_scale(NumericVar *var)
+{
+	int			min_scale;
+	int			last_digit_pos;
+
+	/*
+	 * Ordinarily, the input value will be "stripped" so that the last
+	 * NumericDigit is nonzero.  But we don't want to get into an infinite
+	 * loop if it isn't, so explicitly find the last nonzero digit.
+	 */
+	last_digit_pos = var->ndigits - 1;
+	while (last_digit_pos >= 0 &&
+		   var->digits[last_digit_pos] == 0)
+		last_digit_pos--;
+
+	if (last_digit_pos >= 0)
+	{
+		/* compute min_scale assuming that last ndigit has no zeroes */
+		min_scale = (last_digit_pos - var->weight) * DEC_DIGITS;
+
+		/*
+		 * We could get a negative result if there are no digits after the
+		 * decimal point.  In this case the min_scale must be zero.
+		 */
+		if (min_scale > 0)
+		{
+			/*
+			 * Reduce min_scale if trailing digit(s) in last NumericDigit are
+			 * zero.
+			 */
+			NumericDigit last_digit = var->digits[last_digit_pos];
+
+			while (last_digit % 10 == 0)
+			{
+				min_scale--;
+				last_digit /= 10;
+			}
+		}
+		else
+			min_scale = 0;
+	}
+	else
+		min_scale = 0;			/* result if input is zero */
+
+	return min_scale;
+}
+
+/*
+ * Returns minimum scale required to represent supplied value without loss.
+ */
+Datum
+numeric_min_scale(PG_FUNCTION_ARGS)
+{
+	Numeric		num = PG_GETARG_NUMERIC(0);
+	NumericVar	arg;
+	int			min_scale;
+
+	if (NUMERIC_IS_NAN(num))
+		PG_RETURN_NULL();
+
+	init_var_from_num(num, &arg);
+	min_scale = get_min_scale(&arg);
+	free_var(&arg);
+
+	PG_RETURN_INT32(min_scale);
+}
+
+/*
+ * Reduce scale of numeric value to represent supplied value without loss.
+ */
+Datum
+numeric_trim_scale(PG_FUNCTION_ARGS)
+{
+	Numeric		num = PG_GETARG_NUMERIC(0);
+	Numeric		res;
+	NumericVar	result;
+
+	if (NUMERIC_IS_NAN(num))
+		PG_RETURN_NUMERIC(make_result(&const_nan));
+
+	init_var_from_num(num, &result);
+	result.dscale = get_min_scale(&result);
+	res = make_result(&result);
+	free_var(&result);
+
+	PG_RETURN_NUMERIC(res);
+}
 
 
 /* ----------------------------------------------------------------------
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 8974625bb27..cbd36bcab4c 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201911241
+#define CATALOG_VERSION_NO	202001061
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0b6045acb1e..59f1ff01ab8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4254,6 +4254,13 @@
 { oid => '3281', descr => 'number of decimal digits in the fractional part',
   proname => 'scale', prorettype => 'int4', proargtypes => 'numeric',
   prosrc => 'numeric_scale' },
+{ oid => '8389', descr => 'minimum scale needed to represent the value',
+  proname => 'min_scale', prorettype => 'int4', proargtypes => 'numeric',
+  prosrc => 'numeric_min_scale' },
+{ oid => '8390',
+  descr => 'numeric with minimum scale needed to represent the value',
+  proname => 'trim_scale', prorettype => 'numeric', proargtypes => 'numeric',
+  prosrc => 'numeric_trim_scale' },
 { oid => '1740', descr => 'convert int4 to numeric',
   proname => 'numeric', prorettype => 'numeric', proargtypes => 'int4',
   prosrc => 'int4_numeric' },
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index 1cb3c3bfab7..8acfa394245 100644
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -2079,6 +2079,132 @@ select scale(-13.000000000000000);
 (1 row)
 
 --
+-- Tests for min_scale()
+--
+select min_scale(numeric 'NaN') is NULL; -- should be true
+ ?column? 
+----------
+ t
+(1 row)
+
+select min_scale(0);                     -- no digits
+ min_scale 
+-----------
+         0
+(1 row)
+
+select min_scale(0.00);                  -- no digits again
+ min_scale 
+-----------
+         0
+(1 row)
+
+select min_scale(1.0);                   -- no scale
+ min_scale 
+-----------
+         0
+(1 row)
+
+select min_scale(1.1);                   -- scale 1
+ min_scale 
+-----------
+         1
+(1 row)
+
+select min_scale(1.12);                  -- scale 2
+ min_scale 
+-----------
+         2
+(1 row)
+
+select min_scale(1.123);                 -- scale 3
+ min_scale 
+-----------
+         3
+(1 row)
+
+select min_scale(1.1234);                -- scale 4, filled digit
+ min_scale 
+-----------
+         4
+(1 row)
+
+select min_scale(1.12345);               -- scale 5, 2 NDIGITS
+ min_scale 
+-----------
+         5
+(1 row)
+
+select min_scale(1.1000);                -- 1 pos in NDIGITS
+ min_scale 
+-----------
+         1
+(1 row)
+
+select min_scale(1e100);                 -- very big number
+ min_scale 
+-----------
+         0
+(1 row)
+
+--
+-- Tests for trim_scale()
+--
+select trim_scale(numeric 'NaN');
+ trim_scale 
+------------
+        NaN
+(1 row)
+
+select trim_scale(1.120);
+ trim_scale 
+------------
+       1.12
+(1 row)
+
+select trim_scale(0);
+ trim_scale 
+------------
+          0
+(1 row)
+
+select trim_scale(0.00);
+ trim_scale 
+------------
+          0
+(1 row)
+
+select trim_scale(1.1234500);
+ trim_scale 
+------------
+    1.12345
+(1 row)
+
+select trim_scale(110123.12475871856128000);
+      trim_scale       
+-----------------------
+ 110123.12475871856128
+(1 row)
+
+select trim_scale(-1123.124718561280000000);
+    trim_scale     
+-------------------
+ -1123.12471856128
+(1 row)
+
+select trim_scale(-13.00000000000000000000);
+ trim_scale 
+------------
+        -13
+(1 row)
+
+select trim_scale(1e100);
+                                              trim_scale                                               
+-------------------------------------------------------------------------------------------------------
+ 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
+(1 row)
+
+--
 -- Tests for SUM()
 --
 -- cases that need carry propagation
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index a9394123596..e611cc4d8dc 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -1037,6 +1037,36 @@ select scale(-1123.12471856128);
 select scale(-13.000000000000000);
 
 --
+-- Tests for min_scale()
+--
+
+select min_scale(numeric 'NaN') is NULL; -- should be true
+select min_scale(0);                     -- no digits
+select min_scale(0.00);                  -- no digits again
+select min_scale(1.0);                   -- no scale
+select min_scale(1.1);                   -- scale 1
+select min_scale(1.12);                  -- scale 2
+select min_scale(1.123);                 -- scale 3
+select min_scale(1.1234);                -- scale 4, filled digit
+select min_scale(1.12345);               -- scale 5, 2 NDIGITS
+select min_scale(1.1000);                -- 1 pos in NDIGITS
+select min_scale(1e100);                 -- very big number
+
+--
+-- Tests for trim_scale()
+--
+
+select trim_scale(numeric 'NaN');
+select trim_scale(1.120);
+select trim_scale(0);
+select trim_scale(0.00);
+select trim_scale(1.1234500);
+select trim_scale(110123.12475871856128000);
+select trim_scale(-1123.124718561280000000);
+select trim_scale(-13.00000000000000000000);
+select trim_scale(1e100);
+
+--
 -- Tests for SUM()
 --