ne 10. 11. 2019 v 7:35 odesÃlatel Pavel Stehule <[email protected]>
napsal:
>
>
> so 9. 11. 2019 v 21:34 odesÃlatel Tom Lane <[email protected]> napsal:
>
>> Pavel Stehule <[email protected]> writes:
>> > four years ago Marko Tiikkaja send a patch for numeric_trim functions.
>> This
>> > functions removed ending zeroes from numeric value. This is useful
>> feature,
>> > but there was not any progress on this patch. I think so this feature
>> can
>> > be interesting, so I would to revitalize this patch.
>>
>> > Original discussion
>> >
>> https://www.postgresql-archive.org/Add-numeric-trim-numeric-td5874444.html
>>
>> A more useful link is
>> https://www.postgresql.org/message-id/flat/564D3ADB.7000808%40joh.to
>> and the earlier discussion is at
>> https://www.postgresql.org/message-id/flat/5643125E.1030605%40joh.to
>>
>> Re-reading that thread, I don't really think there's much support for
>> anything beyond the minscale() function. The rest are just inviting
>> confusion with string-related functions. And I really don't like
>> establishing a precedent that btrim() and rtrim() are the same.
>>
>
> I have to agree, so using trim, rtrim names is not best. On second hand,
> probably to most often usage of minscale function will be inside expression
> round(x, minscale(x)), so this functionality can be in core. A question is
> a name.
>
> maybe to_minscale(numeric) ?
>
Here is a patch. It's based on Dean's suggestions.
I implemented two functions - first minscale, second trim_scale. The
overhead of second is minimal - so I think it can be good to have it. I
started design with the name "trim_scale", but the name can be any other.
Regards
Pavel
>
> Regards
>
> Pavel
>
>
>> regards, tom lane
>>
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 28eb322f3f..6f142cd679 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -918,6 +918,19 @@
<entry><literal>6.0000000000</literal></entry>
</row>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>minscale</primary>
+ </indexterm>
+ <literal><function>minscale(<type>numeric</type>)</function></literal>
+ </entry>
+ <entry><type>integer</type></entry>
+ <entry>returns minimal scale of the argument (the number of decimal digits in the fractional part)</entry>
+ <entry><literal>scale(8.4100)</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+
<row>
<entry>
<indexterm>
@@ -1041,6 +1054,19 @@
<entry><literal>1.4142135623731</literal></entry>
</row>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>trim_scale</primary>
+ </indexterm>
+ <literal><function>trim_scale(<type>numeric</type>)</function></literal>
+ </entry>
+ <entry><type>numeric</type></entry>
+ <entry>reduce scale of the argument (the number of decimal digits in the fractional part)</entry>
+ <entry><literal>scale(8.4100)</literal></entry>
+ <entry><literal>8.41</literal></entry>
+ </row>
+
<row>
<entry>
<indexterm>
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index a00db3ce7a..35234aee4c 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -5620,6 +5620,88 @@ int2int4_sum(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(Int64GetDatumFast(transdata->sum));
}
+/*
+ * Calculate minimal display scale. The var should be stripped already.
+ */
+static int
+get_min_scale(NumericVar *var)
+{
+ int minscale = 0;
+
+ if (var->ndigits > 0)
+ {
+ NumericDigit last_digit;
+
+ /* maximal size of minscale, can be lower */
+ minscale = (var->ndigits - var->weight - 1) * DEC_DIGITS;
+
+ /*
+ * When there are not digits after decimal point, the previous expression
+ * can be negative. In this case, the minscale must be zero.
+ */
+ if (minscale > 0)
+ {
+ /* reduce minscale if trailing digits in last numeric digits are zero */
+ last_digit = var->digits[var->ndigits - 1];
+
+ while (last_digit % 10 == 0)
+ {
+ minscale--;
+ last_digit /= 10;
+ }
+ }
+ else
+ minscale = 0;
+ }
+
+ return minscale;
+}
+
+/*
+ * Returns minimal scale of numeric value when value is not changed
+ */
+Datum
+numeric_minscale(PG_FUNCTION_ARGS)
+{
+ Numeric num = PG_GETARG_NUMERIC(0);
+ NumericVar arg;
+ int minscale;
+
+ if (NUMERIC_IS_NAN(num))
+ PG_RETURN_NULL();
+
+ init_var_from_num(num, &arg);
+ strip_var(&arg);
+
+ minscale = get_min_scale(&arg);
+ free_var(&arg);
+
+ PG_RETURN_INT32(minscale);
+}
+
+/*
+ * Reduce scale of numeric value so value is not changed
+ */
+Datum
+numeric_trim_scale(PG_FUNCTION_ARGS)
+{
+ Numeric num = PG_GETARG_NUMERIC(0);
+ Numeric res;
+ NumericVar result;
+
+ if (NUMERIC_IS_NAN(num))
+ PG_RETURN_NULL();
+
+ init_var_from_num(num, &result);
+ strip_var(&result);
+
+ result.dscale = get_min_scale(&result);
+
+ res = make_result(&result);
+ free_var(&result);
+
+ PG_RETURN_NUMERIC(res);
+}
/* ----------------------------------------------------------------------
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 58ea5b982b..e603a5d8dd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4288,6 +4288,12 @@
proname => 'width_bucket', prorettype => 'int4',
proargtypes => 'numeric numeric numeric int4',
prosrc => 'width_bucket_numeric' },
+{ oid => '3434', descr => 'returns minimal scale of numeric value',
+ proname => 'minscale', prorettype => 'int4', proargtypes => 'numeric',
+ prosrc => 'numeric_minscale' },
+{ oid => '3435', descr => 'returns numeric value with minimal scale',
+ proname => 'trim_scale', prorettype => 'numeric', proargtypes => 'numeric',
+ prosrc => 'numeric_trim_scale' },
{ oid => '1747',
proname => 'time_pl_interval', prorettype => 'time',
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index 1cb3c3bfab..778c204b13 100644
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -2094,3 +2094,126 @@ SELECT SUM((-9999)::numeric) FROM generate_series(1, 100000);
-999900000
(1 row)
+--
+-- Tests for minscale and trim_scale
+--
+select minscale(numeric 'NaN');
+ minscale
+----------
+
+(1 row)
+
+select minscale(NULL::numeric);
+ minscale
+----------
+
+(1 row)
+
+select minscale(1.120);
+ minscale
+----------
+ 2
+(1 row)
+
+select minscale(0);
+ minscale
+----------
+ 0
+(1 row)
+
+select minscale(0.00);
+ minscale
+----------
+ 0
+(1 row)
+
+select minscale(1.1234500);
+ minscale
+----------
+ 5
+(1 row)
+
+select minscale(110123.12475871856128000);
+ minscale
+----------
+ 14
+(1 row)
+
+select minscale(-1123.124718561280000000);
+ minscale
+----------
+ 11
+(1 row)
+
+select minscale(-13.00000000000000000000);
+ minscale
+----------
+ 0
+(1 row)
+
+select minscale(1e100);
+ minscale
+----------
+ 0
+(1 row)
+
+select trim_scale(numeric 'NaN');
+ trim_scale
+------------
+
+(1 row)
+
+select trim_scale(NULL::numeric);
+ trim_scale
+------------
+
+(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)
+
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index a939412359..f48740e107 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -1043,3 +1043,28 @@ select scale(-13.000000000000000);
-- cases that need carry propagation
SELECT SUM(9999::numeric) FROM generate_series(1, 100000);
SELECT SUM((-9999)::numeric) FROM generate_series(1, 100000);
+
+--
+-- Tests for minscale and trim_scale
+--
+select minscale(numeric 'NaN');
+select minscale(NULL::numeric);
+select minscale(1.120);
+select minscale(0);
+select minscale(0.00);
+select minscale(1.1234500);
+select minscale(110123.12475871856128000);
+select minscale(-1123.124718561280000000);
+select minscale(-13.00000000000000000000);
+select minscale(1e100);
+
+select trim_scale(numeric 'NaN');
+select trim_scale(NULL::numeric);
+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);
