Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit20d6225

Browse files
committed
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 PincDiscussion:https://postgr.es/m/CAFj8pRDjs-navGASeF0Wk74N36YGFJ+v=Ok9_knRa7vDc-qugg@mail.gmail.com
1 parentb9c130a commit20d6225

File tree

6 files changed

+285
-3
lines changed

6 files changed

+285
-3
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 30 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -918,6 +918,20 @@
918918
<entry><literal>6.0000000000</literal></entry>
919919
</row>
920920

921+
<row>
922+
<entry>
923+
<indexterm>
924+
<primary>min_scale</primary>
925+
</indexterm>
926+
<literal><function>min_scale(<type>numeric</type>)</function></literal>
927+
</entry>
928+
<entry><type>integer</type></entry>
929+
<entry>minimum scale (number of fractional decimal digits) needed
930+
to represent the supplied value</entry>
931+
<entry><literal>min_scale(8.4100)</literal></entry>
932+
<entry><literal>2</literal></entry>
933+
</row>
934+
921935
<row>
922936
<entry>
923937
<indexterm>
@@ -1011,8 +1025,8 @@
10111025
</entry>
10121026
<entry><type>integer</type></entry>
10131027
<entry>scale of the argument (the number of decimal digits in the fractional part)</entry>
1014-
<entry><literal>scale(8.41)</literal></entry>
1015-
<entry><literal>2</literal></entry>
1028+
<entry><literal>scale(8.4100)</literal></entry>
1029+
<entry><literal>4</literal></entry>
10161030
</row>
10171031

10181032
<row>
@@ -1041,6 +1055,20 @@
10411055
<entry><literal>1.4142135623731</literal></entry>
10421056
</row>
10431057

1058+
<row>
1059+
<entry>
1060+
<indexterm>
1061+
<primary>trim_scale</primary>
1062+
</indexterm>
1063+
<literal><function>trim_scale(<type>numeric</type>)</function></literal>
1064+
</entry>
1065+
<entry><type>numeric</type></entry>
1066+
<entry>reduce the scale (number of fractional decimal digits) by
1067+
removing trailing zeroes</entry>
1068+
<entry><literal>trim_scale(8.4100)</literal></entry>
1069+
<entry><literal>8.41</literal></entry>
1070+
</row>
1071+
10441072
<row>
10451073
<entry>
10461074
<indexterm>

‎src/backend/utils/adt/numeric.c

Lines changed: 91 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3179,6 +3179,97 @@ numeric_scale(PG_FUNCTION_ARGS)
31793179
PG_RETURN_INT32(NUMERIC_DSCALE(num));
31803180
}
31813181

3182+
/*
3183+
* Calculate minimum scale for value.
3184+
*/
3185+
staticint
3186+
get_min_scale(NumericVar*var)
3187+
{
3188+
intmin_scale;
3189+
intlast_digit_pos;
3190+
3191+
/*
3192+
* Ordinarily, the input value will be "stripped" so that the last
3193+
* NumericDigit is nonzero. But we don't want to get into an infinite
3194+
* loop if it isn't, so explicitly find the last nonzero digit.
3195+
*/
3196+
last_digit_pos=var->ndigits-1;
3197+
while (last_digit_pos >=0&&
3198+
var->digits[last_digit_pos]==0)
3199+
last_digit_pos--;
3200+
3201+
if (last_digit_pos >=0)
3202+
{
3203+
/* compute min_scale assuming that last ndigit has no zeroes */
3204+
min_scale= (last_digit_pos-var->weight)*DEC_DIGITS;
3205+
3206+
/*
3207+
* We could get a negative result if there are no digits after the
3208+
* decimal point. In this case the min_scale must be zero.
3209+
*/
3210+
if (min_scale>0)
3211+
{
3212+
/*
3213+
* Reduce min_scale if trailing digit(s) in last NumericDigit are
3214+
* zero.
3215+
*/
3216+
NumericDigitlast_digit=var->digits[last_digit_pos];
3217+
3218+
while (last_digit %10==0)
3219+
{
3220+
min_scale--;
3221+
last_digit /=10;
3222+
}
3223+
}
3224+
else
3225+
min_scale=0;
3226+
}
3227+
else
3228+
min_scale=0;/* result if input is zero */
3229+
3230+
returnmin_scale;
3231+
}
3232+
3233+
/*
3234+
* Returns minimum scale required to represent supplied value without loss.
3235+
*/
3236+
Datum
3237+
numeric_min_scale(PG_FUNCTION_ARGS)
3238+
{
3239+
Numericnum=PG_GETARG_NUMERIC(0);
3240+
NumericVararg;
3241+
intmin_scale;
3242+
3243+
if (NUMERIC_IS_NAN(num))
3244+
PG_RETURN_NULL();
3245+
3246+
init_var_from_num(num,&arg);
3247+
min_scale=get_min_scale(&arg);
3248+
free_var(&arg);
3249+
3250+
PG_RETURN_INT32(min_scale);
3251+
}
3252+
3253+
/*
3254+
* Reduce scale of numeric value to represent supplied value without loss.
3255+
*/
3256+
Datum
3257+
numeric_trim_scale(PG_FUNCTION_ARGS)
3258+
{
3259+
Numericnum=PG_GETARG_NUMERIC(0);
3260+
Numericres;
3261+
NumericVarresult;
3262+
3263+
if (NUMERIC_IS_NAN(num))
3264+
PG_RETURN_NUMERIC(make_result(&const_nan));
3265+
3266+
init_var_from_num(num,&result);
3267+
result.dscale=get_min_scale(&result);
3268+
res=make_result(&result);
3269+
free_var(&result);
3270+
3271+
PG_RETURN_NUMERIC(res);
3272+
}
31823273

31833274

31843275
/* ----------------------------------------------------------------------

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201911241
56+
#defineCATALOG_VERSION_NO202001061
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4254,6 +4254,13 @@
42544254
{ oid => '3281', descr => 'number of decimal digits in the fractional part',
42554255
proname => 'scale', prorettype => 'int4', proargtypes => 'numeric',
42564256
prosrc => 'numeric_scale' },
4257+
{ oid => '8389', descr => 'minimum scale needed to represent the value',
4258+
proname => 'min_scale', prorettype => 'int4', proargtypes => 'numeric',
4259+
prosrc => 'numeric_min_scale' },
4260+
{ oid => '8390',
4261+
descr => 'numeric with minimum scale needed to represent the value',
4262+
proname => 'trim_scale', prorettype => 'numeric', proargtypes => 'numeric',
4263+
prosrc => 'numeric_trim_scale' },
42574264
{ oid => '1740', descr => 'convert int4 to numeric',
42584265
proname => 'numeric', prorettype => 'numeric', proargtypes => 'int4',
42594266
prosrc => 'int4_numeric' },

‎src/test/regress/expected/numeric.out

Lines changed: 126 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2078,6 +2078,132 @@ select scale(-13.000000000000000);
20782078
15
20792079
(1 row)
20802080

2081+
--
2082+
-- Tests for min_scale()
2083+
--
2084+
select min_scale(numeric 'NaN') is NULL; -- should be true
2085+
?column?
2086+
----------
2087+
t
2088+
(1 row)
2089+
2090+
select min_scale(0); -- no digits
2091+
min_scale
2092+
-----------
2093+
0
2094+
(1 row)
2095+
2096+
select min_scale(0.00); -- no digits again
2097+
min_scale
2098+
-----------
2099+
0
2100+
(1 row)
2101+
2102+
select min_scale(1.0); -- no scale
2103+
min_scale
2104+
-----------
2105+
0
2106+
(1 row)
2107+
2108+
select min_scale(1.1); -- scale 1
2109+
min_scale
2110+
-----------
2111+
1
2112+
(1 row)
2113+
2114+
select min_scale(1.12); -- scale 2
2115+
min_scale
2116+
-----------
2117+
2
2118+
(1 row)
2119+
2120+
select min_scale(1.123); -- scale 3
2121+
min_scale
2122+
-----------
2123+
3
2124+
(1 row)
2125+
2126+
select min_scale(1.1234); -- scale 4, filled digit
2127+
min_scale
2128+
-----------
2129+
4
2130+
(1 row)
2131+
2132+
select min_scale(1.12345); -- scale 5, 2 NDIGITS
2133+
min_scale
2134+
-----------
2135+
5
2136+
(1 row)
2137+
2138+
select min_scale(1.1000); -- 1 pos in NDIGITS
2139+
min_scale
2140+
-----------
2141+
1
2142+
(1 row)
2143+
2144+
select min_scale(1e100); -- very big number
2145+
min_scale
2146+
-----------
2147+
0
2148+
(1 row)
2149+
2150+
--
2151+
-- Tests for trim_scale()
2152+
--
2153+
select trim_scale(numeric 'NaN');
2154+
trim_scale
2155+
------------
2156+
NaN
2157+
(1 row)
2158+
2159+
select trim_scale(1.120);
2160+
trim_scale
2161+
------------
2162+
1.12
2163+
(1 row)
2164+
2165+
select trim_scale(0);
2166+
trim_scale
2167+
------------
2168+
0
2169+
(1 row)
2170+
2171+
select trim_scale(0.00);
2172+
trim_scale
2173+
------------
2174+
0
2175+
(1 row)
2176+
2177+
select trim_scale(1.1234500);
2178+
trim_scale
2179+
------------
2180+
1.12345
2181+
(1 row)
2182+
2183+
select trim_scale(110123.12475871856128000);
2184+
trim_scale
2185+
-----------------------
2186+
110123.12475871856128
2187+
(1 row)
2188+
2189+
select trim_scale(-1123.124718561280000000);
2190+
trim_scale
2191+
-------------------
2192+
-1123.12471856128
2193+
(1 row)
2194+
2195+
select trim_scale(-13.00000000000000000000);
2196+
trim_scale
2197+
------------
2198+
-13
2199+
(1 row)
2200+
2201+
select trim_scale(1e100);
2202+
trim_scale
2203+
-------------------------------------------------------------------------------------------------------
2204+
10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
2205+
(1 row)
2206+
20812207
--
20822208
-- Tests for SUM()
20832209
--

‎src/test/regress/sql/numeric.sql

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1036,6 +1036,36 @@ select scale(110123.12475871856128);
10361036
select scale(-1123.12471856128);
10371037
select scale(-13.000000000000000);
10381038

1039+
--
1040+
-- Tests for min_scale()
1041+
--
1042+
1043+
select min_scale(numeric'NaN') isNULL;-- should be true
1044+
select min_scale(0);-- no digits
1045+
select min_scale(0.00);-- no digits again
1046+
select min_scale(1.0);-- no scale
1047+
select min_scale(1.1);-- scale 1
1048+
select min_scale(1.12);-- scale 2
1049+
select min_scale(1.123);-- scale 3
1050+
select min_scale(1.1234);-- scale 4, filled digit
1051+
select min_scale(1.12345);-- scale 5, 2 NDIGITS
1052+
select min_scale(1.1000);-- 1 pos in NDIGITS
1053+
select min_scale(1e100);-- very big number
1054+
1055+
--
1056+
-- Tests for trim_scale()
1057+
--
1058+
1059+
select trim_scale(numeric'NaN');
1060+
select trim_scale(1.120);
1061+
select trim_scale(0);
1062+
select trim_scale(0.00);
1063+
select trim_scale(1.1234500);
1064+
select trim_scale(110123.12475871856128000);
1065+
select trim_scale(-1123.124718561280000000);
1066+
select trim_scale(-13.00000000000000000000);
1067+
select trim_scale(1e100);
1068+
10391069
--
10401070
-- Tests for SUM()
10411071
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp