diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 57a1539506..e7de1837e2 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -918,6 +918,20 @@ 6.0000000000 + + + + minscale + + minscale(numeric) + + integer + minimal scale (number of decimal digits in the fractional part) needed + to store the supplied value without data loss + scale(8.4100) + 2 + + @@ -1041,6 +1055,20 @@ 1.4142135623731 + + + + trim_scale + + trim_scale(numeric) + + numeric + reduce the scale (the number of decimal digits in the fractional part) + to the minimum needed to store the supplied value without data loss + scale(8.4100) + 8.41 + + diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c index a00db3ce7a..085651d51d 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 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 required to represent supplied value without loss. + */ +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 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); + 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 ac8f64b219..1c66b8dd44 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -4254,6 +4254,12 @@ { oid => '3281', descr => 'number of decimal digits in the fractional part', proname => 'scale', prorettype => 'int4', proargtypes => 'numeric', prosrc => 'numeric_scale' }, +{ oid => '3434', descr => 'minimal scale needed to store the supplied value without data loss', + proname => 'minscale', prorettype => 'int4', proargtypes => 'numeric', + prosrc => 'numeric_minscale' }, +{ oid => '3435', descr => 'numeric with minimal scale needed to represent the given 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 1cb3c3bfab..19a2cccde3 100644 --- a/src/test/regress/expected/numeric.out +++ b/src/test/regress/expected/numeric.out @@ -2094,3 +2094,129 @@ SELECT SUM((-9999)::numeric) FROM generate_series(1, 100000); -999900000 (1 row) +-- +-- Tests for minscale() +-- +select minscale(numeric 'NaN') IS NULL; -- should be true + ?column? +---------- + t +(1 row) + +select minscale(NULL::numeric) IS NULL; -- should be true + ?column? +---------- + t +(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) + +-- +-- Tests for trim_scale() +-- +select trim_scale(numeric 'NaN') = 'NaN':: numeric; -- should be true + ?column? +---------- + t +(1 row) + +select trim_scale(NULL::numeric) IS NULL; -- should be true + ?column? +---------- + t +(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..5fbcf20a5b 100644 --- a/src/test/regress/sql/numeric.sql +++ b/src/test/regress/sql/numeric.sql @@ -1043,3 +1043,33 @@ 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() +-- + +select minscale(numeric 'NaN') IS NULL; -- should be true +select minscale(NULL::numeric) IS NULL; -- should be true +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); + +-- +-- Tests for trim_scale() +-- + +select trim_scale(numeric 'NaN') = 'NaN':: numeric; -- should be true +select trim_scale(NULL::numeric) IS NULL; -- should be true +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);