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

Commitf5e524d

Browse files
committed
Add casts from int4 and int8 to numeric.
Joey Adams, per gripe from Ramanujam. Review by myself and Tom Lane.
1 parent88f32b7 commitf5e524d

File tree

8 files changed

+161
-7
lines changed

8 files changed

+161
-7
lines changed

‎doc/src/sgml/datatype.sgml

Lines changed: 12 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -886,15 +886,22 @@ ALTER SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceab
886886
</para>
887887

888888
<para>
889-
Values of the <type>numeric</type> data type can be cast to
890-
<type>money</type>. Other numeric types can be converted to
891-
<type>money</type> by casting to <type>numeric</type> first, for example:
889+
Values of the <type>numeric</type>, <type>int</type>, and
890+
<type>bigint</type> data types can be cast to <type>money</type>.
891+
Conversion from the <type>real</type> and <type>double precision</type>
892+
data types can be done by casting to <type>numeric</type> first, for
893+
example:
892894
<programlisting>
893-
SELECT1234::numeric::money;
895+
SELECT'12.34'::float8::numeric::money;
894896
</programlisting>
897+
However, this is not recommended. Floating point numbers should not be
898+
used to handle money due to the potential for rounding errors.
899+
</para>
900+
901+
<para>
895902
A <type>money</type> value can be cast to <type>numeric</type> without
896903
loss of precision. Conversion to other types could potentially lose
897-
precision, anditmust be done in two stages, for example:
904+
precision, and mustalsobe done in two stages:
898905
<programlisting>
899906
SELECT '52093.89'::money::numeric::float8;
900907
</programlisting>

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

Lines changed: 61 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@
2626
#include"libpq/pqformat.h"
2727
#include"utils/builtins.h"
2828
#include"utils/cash.h"
29+
#include"utils/int8.h"
2930
#include"utils/numeric.h"
3031
#include"utils/pg_locale.h"
3132

@@ -92,7 +93,6 @@ num_word(Cash value)
9293
returnbuf;
9394
}/* num_word() */
9495

95-
9696
/* cash_in()
9797
* Convert a string to a cash data type.
9898
* Format is [$]###[,]###[.##]
@@ -938,3 +938,63 @@ numeric_cash(PG_FUNCTION_ARGS)
938938

939939
PG_RETURN_CASH(result);
940940
}
941+
942+
/* int4_cash()
943+
* Convert int4 (int) to cash
944+
*/
945+
Datum
946+
int4_cash(PG_FUNCTION_ARGS)
947+
{
948+
int32amount=PG_GETARG_INT32(0);
949+
Cashresult;
950+
intfpoint;
951+
int64scale;
952+
inti;
953+
structlconv*lconvert=PGLC_localeconv();
954+
955+
/* see comments about frac_digits in cash_in() */
956+
fpoint=lconvert->frac_digits;
957+
if (fpoint<0||fpoint>10)
958+
fpoint=2;
959+
960+
/* compute required scale factor */
961+
scale=1;
962+
for (i=0;i<fpoint;i++)
963+
scale *=10;
964+
965+
/* compute amount * scale, checking for overflow */
966+
result=DatumGetInt64(DirectFunctionCall2(int8mul,Int64GetDatum(amount),
967+
Int64GetDatum(scale)));
968+
969+
PG_RETURN_CASH(result);
970+
}
971+
972+
/* int8_cash()
973+
* Convert int8 (bigint) to cash
974+
*/
975+
Datum
976+
int8_cash(PG_FUNCTION_ARGS)
977+
{
978+
int64amount=PG_GETARG_INT64(0);
979+
Cashresult;
980+
intfpoint;
981+
int64scale;
982+
inti;
983+
structlconv*lconvert=PGLC_localeconv();
984+
985+
/* see comments about frac_digits in cash_in() */
986+
fpoint=lconvert->frac_digits;
987+
if (fpoint<0||fpoint>10)
988+
fpoint=2;
989+
990+
/* compute required scale factor */
991+
scale=1;
992+
for (i=0;i<fpoint;i++)
993+
scale *=10;
994+
995+
/* compute amount * scale, checking for overflow */
996+
result=DatumGetInt64(DirectFunctionCall2(int8mul,Int64GetDatum(amount),
997+
Int64GetDatum(scale)));
998+
999+
PG_RETURN_CASH(result);
1000+
}

‎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_NO201103201
56+
#defineCATALOG_VERSION_NO201104051
5757

5858
#endif

‎src/include/catalog/pg_cast.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -126,6 +126,8 @@ DATA(insert ( 1700700 1745 i f ));
126126
DATA(insert (17007011746if ));
127127
DATA(insert (79017003823af ));
128128
DATA(insert (17007903824af ));
129+
DATA(insert (237903811af ));
130+
DATA(insert (207903812af ));
129131

130132
/* Allow explicit coercions between int4 and bool */
131133
DATA(insert (23162557ef ));

‎src/include/catalog/pg_proc.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -971,6 +971,10 @@ DATA(insert OID = 3823 ( numeric PGNSP PGUID 12 1 0 0 f f f t f s 1 0 1700
971971
DESCR("convert money to numeric");
972972
DATA(insertOID=3824 (moneyPGNSPPGUID12100ffftfs10790"1700"_null__null__null__null_numeric_cash_null__null__null_ ));
973973
DESCR("convert numeric to money");
974+
DATA(insertOID=3811 (moneyPGNSPPGUID12100ffftfs10790"23"_null__null__null__null_int4_cash_null__null__null_ ));
975+
DESCR("convert int4 to money");
976+
DATA(insertOID=3812 (moneyPGNSPPGUID12100ffftfs10790"20"_null__null__null__null_int8_cash_null__null__null_ ));
977+
DESCR("convert int8 to money");
974978

975979
/* OIDS 900 - 999 */
976980

‎src/include/utils/cash.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -67,4 +67,7 @@ extern Datum cash_words(PG_FUNCTION_ARGS);
6767
externDatumcash_numeric(PG_FUNCTION_ARGS);
6868
externDatumnumeric_cash(PG_FUNCTION_ARGS);
6969

70+
externDatumint4_cash(PG_FUNCTION_ARGS);
71+
externDatumint8_cash(PG_FUNCTION_ARGS);
72+
7073
#endif/* CASH_H */

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

Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -185,3 +185,66 @@ SELECT * FROM money_data;
185185
$123.46
186186
(1 row)
187187

188+
-- Cast int4/int8 to money
189+
SELECT 1234567890::money;
190+
money
191+
-------------------
192+
$1,234,567,890.00
193+
(1 row)
194+
195+
SELECT 12345678901234567::money;
196+
money
197+
----------------------------
198+
$12,345,678,901,234,567.00
199+
(1 row)
200+
201+
SELECT 123456789012345678::money;
202+
ERROR: bigint out of range
203+
SELECT 9223372036854775807::money;
204+
ERROR: bigint out of range
205+
SELECT (-12345)::money;
206+
money
207+
-------------
208+
-$12,345.00
209+
(1 row)
210+
211+
SELECT (-1234567890)::money;
212+
money
213+
--------------------
214+
-$1,234,567,890.00
215+
(1 row)
216+
217+
SELECT (-12345678901234567)::money;
218+
money
219+
-----------------------------
220+
-$12,345,678,901,234,567.00
221+
(1 row)
222+
223+
SELECT (-123456789012345678)::money;
224+
ERROR: bigint out of range
225+
SELECT (-9223372036854775808)::money;
226+
ERROR: bigint out of range
227+
SELECT 1234567890::int4::money;
228+
money
229+
-------------------
230+
$1,234,567,890.00
231+
(1 row)
232+
233+
SELECT 12345678901234567::int8::money;
234+
money
235+
----------------------------
236+
$12,345,678,901,234,567.00
237+
(1 row)
238+
239+
SELECT (-1234567890)::int4::money;
240+
money
241+
--------------------
242+
-$1,234,567,890.00
243+
(1 row)
244+
245+
SELECT (-12345678901234567)::int8::money;
246+
money
247+
-----------------------------
248+
-$12,345,678,901,234,567.00
249+
(1 row)
250+

‎src/test/regress/sql/money.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -56,3 +56,18 @@ SELECT * FROM money_data;
5656
DELETEFROM money_data;
5757
INSERT INTO money_dataVALUES ('$123.459');
5858
SELECT*FROM money_data;
59+
60+
-- Cast int4/int8 to money
61+
SELECT1234567890::money;
62+
SELECT12345678901234567::money;
63+
SELECT123456789012345678::money;
64+
SELECT9223372036854775807::money;
65+
SELECT (-12345)::money;
66+
SELECT (-1234567890)::money;
67+
SELECT (-12345678901234567)::money;
68+
SELECT (-123456789012345678)::money;
69+
SELECT (-9223372036854775808)::money;
70+
SELECT1234567890::int4::money;
71+
SELECT12345678901234567::int8::money;
72+
SELECT (-1234567890)::int4::money;
73+
SELECT (-12345678901234567)::int8::money;

0 commit comments

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