Hi,
As suggested in 5643125e.1030...@joh.to, here's a patch for extracting
the scale out of a numeric.
This is 2016-01 CF material, but if someone wants to bas^H^H^Hsay nice
things in the meanwhile, feel free.
.m
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 852,857 ****
--- 852,870 ----
<row>
<entry>
<indexterm>
+ <primary>scale</primary>
+ </indexterm>
+ <literal><function>scale(<type>numeric</type>)</function></literal>
+ </entry>
+ <entry><type>numeric</type></entry>
+ <entry>scale of the argument (the number of decimal digits in the
fractional part)</entry>
+ <entry><literal>scale(8.41)</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>sign</primary>
</indexterm>
<literal><function>sign(<type>dp</type> or
<type>numeric</type>)</function></literal>
*** a/src/backend/utils/adt/numeric.c
--- b/src/backend/utils/adt/numeric.c
***************
*** 2825,2830 **** numeric_power(PG_FUNCTION_ARGS)
--- 2825,2847 ----
PG_RETURN_NUMERIC(res);
}
+ /*
+ * numeric_scale() -
+ *
+ * Returns the scale, i.e. the count of decimal digits in the fractional
part
+ */
+ Datum
+ numeric_scale(PG_FUNCTION_ARGS)
+ {
+ Numeric num = PG_GETARG_NUMERIC(0);
+
+ if (NUMERIC_IS_NAN(num))
+ PG_RETURN_NULL();
+
+ PG_RETURN_INT32(NUMERIC_DSCALE(num));
+ }
+
+
/* ----------------------------------------------------------------------
*
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2361,2366 **** DESCR("exponentiation");
--- 2361,2368 ----
DATA(insert OID = 2169 ( power PGNSP
PGUID 12 1 0 0 0 f f f f t f i s 2 0 1700 "1700 1700" _null_ _null_ _null_
_null_ _null_ numeric_power _null_ _null_ _null_ ));
DESCR("exponentiation");
DATA(insert OID = 1739 ( numeric_power PGNSP PGUID 12
1 0 0 0 f f f f t f i s 2 0 1700 "1700 1700" _null_ _null_ _null_ _null_ _null_
numeric_power _null_ _null_ _null_ ));
+ DATA(insert OID = 8888 ( scale PGNSP PGUID 12 1 0 0 0 f f f f t f i
s 1 0 23 "1700" _null_ _null_ _null_ _null_ _null_ numeric_scale _null_
_null_ _null_ ));
+ DESCR("returns the number of decimal digits in the fractional part");
DATA(insert OID = 1740 ( numeric PGNSP PGUID 12
1 0 0 0 f f f f t f i s 1 0 1700 "23" _null_ _null_ _null_ _null_ _null_
int4_numeric _null_ _null_ _null_ ));
DESCR("convert int4 to numeric");
DATA(insert OID = 1741 ( log PGNSP PGUID 14
1 0 0 0 f f f f t f i s 1 0 1700 "1700" _null_ _null_ _null_ _null_ _null_
"select pg_catalog.log(10, $1)" _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 1022,1027 **** extern Datum numeric_exp(PG_FUNCTION_ARGS);
--- 1022,1028 ----
extern Datum numeric_ln(PG_FUNCTION_ARGS);
extern Datum numeric_log(PG_FUNCTION_ARGS);
extern Datum numeric_power(PG_FUNCTION_ARGS);
+ extern Datum numeric_scale(PG_FUNCTION_ARGS);
extern Datum int4_numeric(PG_FUNCTION_ARGS);
extern Datum numeric_int4(PG_FUNCTION_ARGS);
extern Datum int8_numeric(PG_FUNCTION_ARGS);
*** a/src/test/regress/expected/numeric.out
--- b/src/test/regress/expected/numeric.out
***************
*** 1852,1854 **** select log(3.1954752e47, 9.4792021e-73);
--- 1852,1911 ----
-1.51613372350688302142917386143459361608600157692779164475351842333265418126982165
(1 row)
+ --
+ -- Tests for scale()
+ --
+ select scale(numeric 'NaN');
+ scale
+ -------
+
+ (1 row)
+
+ select scale(NULL::numeric);
+ scale
+ -------
+
+ (1 row)
+
+ select scale(1.12);
+ scale
+ -------
+ 2
+ (1 row)
+
+ select scale(0);
+ scale
+ -------
+ 0
+ (1 row)
+
+ select scale(0.00);
+ scale
+ -------
+ 2
+ (1 row)
+
+ select scale(1.12345);
+ scale
+ -------
+ 5
+ (1 row)
+
+ select scale(110123.12475871856128);
+ scale
+ -------
+ 14
+ (1 row)
+
+ select scale(-1123.12471856128);
+ scale
+ -------
+ 11
+ (1 row)
+
+ select scale(-13.000000000000000);
+ scale
+ -------
+ 15
+ (1 row)
+
*** a/src/test/regress/sql/numeric.sql
--- b/src/test/regress/sql/numeric.sql
***************
*** 983,985 **** select log(1.23e-89, 6.4689e45);
--- 983,999 ----
select log(0.99923, 4.58934e34);
select log(1.000016, 8.452010e18);
select log(3.1954752e47, 9.4792021e-73);
+
+ --
+ -- Tests for scale()
+ --
+
+ select scale(numeric 'NaN');
+ select scale(NULL::numeric);
+ select scale(1.12);
+ select scale(0);
+ select scale(0.00);
+ select scale(1.12345);
+ select scale(110123.12475871856128);
+ select scale(-1123.12471856128);
+ select scale(-13.000000000000000);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers