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

Commit7590ddb

Browse files
committed
Add support for dividing money by money (yielding a float8 result) and for
casting between money and numeric.Andy Balholm, reviewed by Kevin Grittner
1 parente11cfa8 commit7590ddb

File tree

7 files changed

+148
-34
lines changed

7 files changed

+148
-34
lines changed

‎doc/src/sgml/datatype.sgml‎

Lines changed: 31 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.250 2010/07/03 04:03:06 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.251 2010/07/16 02:15:53 tgl Exp $ -->
22

33
<chapter id="datatype">
44
<title>Data Types</title>
@@ -839,32 +839,11 @@ ALTER SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceab
839839
fractional precision; see <xref
840840
linkend="datatype-money-table">. The fractional precision is
841841
determined by the database's <xref linkend="guc-lc-monetary"> setting.
842+
The range shown in the table assumes there are two fractional digits.
842843
Input is accepted in a variety of formats, including integer and
843844
floating-point literals, as well as typical
844845
currency formatting, such as <literal>'$1,000.00'</literal>.
845846
Output is generally in the latter form but depends on the locale.
846-
Non-quoted numeric values can be converted to <type>money</type> by
847-
casting the numeric value to <type>text</type> and then
848-
<type>money</type>, for example:
849-
<programlisting>
850-
SELECT 1234::text::money;
851-
</programlisting>
852-
There is no simple way of doing the reverse in a locale-independent
853-
manner, namely casting a <type>money</type> value to a numeric type.
854-
If you know the currency symbol and thousands separator you can use
855-
<function>regexp_replace()</>:
856-
<programlisting>
857-
SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric;
858-
</programlisting>
859-
860-
</para>
861-
862-
<para>
863-
Since the output of this data type is locale-sensitive, it might not
864-
work to load <type>money</> data into a database that has a different
865-
setting of <varname>lc_monetary</>. To avoid problems, before
866-
restoring a dump into a new database make sure <varname>lc_monetary</> has the same or
867-
equivalent value as in the database that was dumped.
868847
</para>
869848

870849
<table id="datatype-money-table">
@@ -888,6 +867,35 @@ SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric;
888867
</tbody>
889868
</tgroup>
890869
</table>
870+
871+
<para>
872+
Since the output of this data type is locale-sensitive, it might not
873+
work to load <type>money</> data into a database that has a different
874+
setting of <varname>lc_monetary</>. To avoid problems, before
875+
restoring a dump into a new database make sure <varname>lc_monetary</> has
876+
the same or equivalent value as in the database that was dumped.
877+
</para>
878+
879+
<para>
880+
Values of the <type>numeric</type> data type can be cast to
881+
<type>money</type>. Other numeric types can be converted to
882+
<type>money</type> by casting to <type>numeric</type> first, for example:
883+
<programlisting>
884+
SELECT 1234::numeric::money;
885+
</programlisting>
886+
A <type>money</type> value can be cast to <type>numeric</type> without
887+
loss of precision. Conversion to other types could potentially lose
888+
precision, and it must be done in two stages, for example:
889+
<programlisting>
890+
SELECT '52093.89'::money::numeric::float8;
891+
</programlisting>
892+
</para>
893+
894+
<para>
895+
When a <type>money</type> value is divided by another <type>money</type>
896+
value, the result is <type>double precision</type> (i.e., a pure number,
897+
not money); the currency units cancel each other out in the division.
898+
</para>
891899
</sect1>
892900

893901

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

Lines changed: 96 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
* this version handles 64 bit numbers and so can hold values up to
1414
* $92,233,720,368,547,758.07.
1515
*
16-
* $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.82 2009/06/11 14:49:03 momjian Exp $
16+
* $PostgreSQL: pgsql/src/backend/utils/adt/cash.c,v 1.83 2010/07/16 02:15:53 tgl Exp $
1717
*/
1818

1919
#include"postgres.h"
@@ -26,6 +26,7 @@
2626
#include"libpq/pqformat.h"
2727
#include"utils/builtins.h"
2828
#include"utils/cash.h"
29+
#include"utils/numeric.h"
2930
#include"utils/pg_locale.h"
3031

3132
#defineCASH_BUFSZ36
@@ -114,7 +115,6 @@ cash_in(PG_FUNCTION_ARGS)
114115
psymbol;
115116
constchar*nsymbol,
116117
*csymbol;
117-
118118
structlconv*lconvert=PGLC_localeconv();
119119

120120
/*
@@ -263,7 +263,6 @@ cash_out(PG_FUNCTION_ARGS)
263263
*nsymbol;
264264
chardsymbol;
265265
charconvention;
266-
267266
structlconv*lconvert=PGLC_localeconv();
268267

269268
/* see comments about frac_digits in cash_in() */
@@ -478,6 +477,26 @@ cash_mi(PG_FUNCTION_ARGS)
478477
}
479478

480479

480+
/* cash_div_cash()
481+
* Divide cash by cash, returning float8.
482+
*/
483+
Datum
484+
cash_div_cash(PG_FUNCTION_ARGS)
485+
{
486+
Cashdividend=PG_GETARG_CASH(0);
487+
Cashdivisor=PG_GETARG_CASH(1);
488+
float8quotient;
489+
490+
if (divisor==0)
491+
ereport(ERROR,
492+
(errcode(ERRCODE_DIVISION_BY_ZERO),
493+
errmsg("division by zero")));
494+
495+
quotient= (float8)dividend / (float8)divisor;
496+
PG_RETURN_FLOAT8(quotient);
497+
}
498+
499+
481500
/* cash_mul_flt8()
482501
* Multiply cash by float8.
483502
*/
@@ -845,3 +864,77 @@ cash_words(PG_FUNCTION_ARGS)
845864
/* return as text datum */
846865
PG_RETURN_TEXT_P(cstring_to_text(buf));
847866
}
867+
868+
869+
/* cash_numeric()
870+
* Convert cash to numeric.
871+
*/
872+
Datum
873+
cash_numeric(PG_FUNCTION_ARGS)
874+
{
875+
Cashmoney=PG_GETARG_CASH(0);
876+
Numericresult;
877+
intfpoint;
878+
int64scale;
879+
inti;
880+
Datumamount;
881+
Datumnumeric_scale;
882+
Datumquotient;
883+
structlconv*lconvert=PGLC_localeconv();
884+
885+
/* see comments about frac_digits in cash_in() */
886+
fpoint=lconvert->frac_digits;
887+
if (fpoint<0||fpoint>10)
888+
fpoint=2;
889+
890+
/* compute required scale factor */
891+
scale=1;
892+
for (i=0;i<fpoint;i++)
893+
scale *=10;
894+
895+
/* form the result as money / scale */
896+
amount=DirectFunctionCall1(int8_numeric,Int64GetDatum(money));
897+
numeric_scale=DirectFunctionCall1(int8_numeric,Int64GetDatum(scale));
898+
quotient=DirectFunctionCall2(numeric_div,amount,numeric_scale);
899+
900+
/* forcibly round to exactly the intended number of digits */
901+
result=DatumGetNumeric(DirectFunctionCall2(numeric_round,
902+
quotient,
903+
Int32GetDatum(fpoint)));
904+
905+
PG_RETURN_NUMERIC(result);
906+
}
907+
908+
/* numeric_cash()
909+
* Convert numeric to cash.
910+
*/
911+
Datum
912+
numeric_cash(PG_FUNCTION_ARGS)
913+
{
914+
Datumamount=PG_GETARG_DATUM(0);
915+
Cashresult;
916+
intfpoint;
917+
int64scale;
918+
inti;
919+
Datumnumeric_scale;
920+
structlconv*lconvert=PGLC_localeconv();
921+
922+
/* see comments about frac_digits in cash_in() */
923+
fpoint=lconvert->frac_digits;
924+
if (fpoint<0||fpoint>10)
925+
fpoint=2;
926+
927+
/* compute required scale factor */
928+
scale=1;
929+
for (i=0;i<fpoint;i++)
930+
scale *=10;
931+
932+
/* multiply the input amount by scale factor */
933+
numeric_scale=DirectFunctionCall1(int8_numeric,Int64GetDatum(scale));
934+
amount=DirectFunctionCall2(numeric_mul,amount,numeric_scale);
935+
936+
/* note that numeric_int8 will round to nearest integer for us */
937+
result=DatumGetInt64(DirectFunctionCall1(numeric_int8,amount));
938+
939+
PG_RETURN_CASH(result);
940+
}

‎src/include/catalog/catversion.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.587 2010/04/26 14:22:37 momjian Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.588 2010/07/16 02:15:54 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201004261
56+
#defineCATALOG_VERSION_NO201007151
5757

5858
#endif

‎src/include/catalog/pg_cast.h‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
* Copyright (c) 2002-2010, PostgreSQL Global Development Group
1212
*
13-
* $PostgreSQL: pgsql/src/include/catalog/pg_cast.h,v 1.44 2010/01/05 01:06:56 tgl Exp $
13+
* $PostgreSQL: pgsql/src/include/catalog/pg_cast.h,v 1.45 2010/07/16 02:15:54 tgl Exp $
1414
*
1515
* NOTES
1616
* the genbki.pl script reads this file and generates .bki
@@ -124,6 +124,8 @@ DATA(insert ( 1700 21 1783 a f ));
124124
DATA(insert (1700231744af ));
125125
DATA(insert (17007001745if ));
126126
DATA(insert (17007011746if ));
127+
DATA(insert (79017003823af ));
128+
DATA(insert (17007903824af ));
127129

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

‎src/include/catalog/pg_operator.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.170 2010/01/14 16:31:09 teodor Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.171 2010/07/16 02:15:54 tgl Exp $
1212
*
1313
* NOTES
1414
* the genbki.pl script reads this file and generates .bki
@@ -415,6 +415,7 @@ DATA(insert OID = 915 ( "/" PGNSP PGUID b f f790 21790 0 0 cash_div_
415415
DATA(insertOID=916 ("*"PGNSPPGUIDbff7017907909080flt8_mul_cash-- ));
416416
DATA(insertOID=917 ("*"PGNSPPGUIDbff237907909120int4_mul_cash-- ));
417417
DATA(insertOID=918 ("*"PGNSPPGUIDbff217907909140int2_mul_cash-- ));
418+
DATA(insertOID=3825 ("/"PGNSPPGUIDbff79079070100cash_div_cash-- ));
418419

419420
DATA(insertOID=965 ("^"PGNSPPGUIDbff70170170100dpow-- ));
420421
DATA(insertOID=966 ("+"PGNSPPGUIDbff10341033103400aclinsert-- ));

‎src/include/catalog/pg_proc.h‎

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.571 2010/05/27 16:20:11 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.572 2010/07/16 02:15:54 tgl Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.pl reads this file and generates .bki
@@ -1165,9 +1165,9 @@ DESCR("multiply");
11651165
DATA(insertOID=867 (cash_div_int2PGNSPPGUID12100ffftfi20790"790 21"_null__null__null__null_cash_div_int2_null__null__null_ ));
11661166
DESCR("divide");
11671167

1168-
DATA(insertOID=886 (cash_inPGNSPPGUID12100ffftfi10790"2275"_null__null__null__null_cash_in_null__null__null_ ));
1168+
DATA(insertOID=886 (cash_inPGNSPPGUID12100ffftfs10790"2275"_null__null__null__null_cash_in_null__null__null_ ));
11691169
DESCR("I/O");
1170-
DATA(insertOID=887 (cash_outPGNSPPGUID12100ffftfi102275"790"_null__null__null__null_cash_out_null__null__null_ ));
1170+
DATA(insertOID=887 (cash_outPGNSPPGUID12100ffftfs102275"790"_null__null__null__null_cash_out_null__null__null_ ));
11711171
DESCR("I/O");
11721172
DATA(insertOID=888 (cash_eqPGNSPPGUID12100ffftfi2016"790 790"_null__null__null__null_cash_eq_null__null__null_ ));
11731173
DESCR("equal");
@@ -1197,6 +1197,12 @@ DATA(insert OID = 919 ( flt8_mul_cash PGNSP PGUID 12 1 0 0 f f f t f i 2 0
11971197
DESCR("multiply");
11981198
DATA(insertOID=935 (cash_wordsPGNSPPGUID12100ffftfi1025"790"_null__null__null__null_cash_words_null__null__null_ ));
11991199
DESCR("output amount as words");
1200+
DATA(insertOID=3822 (cash_div_cashPGNSPPGUID12100ffftfi20701"790 790"_null__null__null__null_cash_div_cash_null__null__null_ ));
1201+
DESCR("divide");
1202+
DATA(insertOID=3823 (numericPGNSPPGUID12100ffftfs101700"790"_null__null__null__null_cash_numeric_null__null__null_ ));
1203+
DESCR("(internal)");
1204+
DATA(insertOID=3824 (moneyPGNSPPGUID12100ffftfs10790"1700"_null__null__null__null_numeric_cash_null__null__null_ ));
1205+
DESCR("(internal)");
12001206

12011207
/* OIDS 900 - 999 */
12021208

‎src/include/utils/cash.h‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
/*
2-
* $PostgreSQL: pgsql/src/include/utils/cash.h,v 1.27 2009/06/11 14:49:13 momjian Exp $
2+
* $PostgreSQL: pgsql/src/include/utils/cash.h,v 1.28 2010/07/16 02:15:56 tgl Exp $
33
*
44
*
55
* cash.h
@@ -37,6 +37,7 @@ extern Datum cash_cmp(PG_FUNCTION_ARGS);
3737

3838
externDatumcash_pl(PG_FUNCTION_ARGS);
3939
externDatumcash_mi(PG_FUNCTION_ARGS);
40+
externDatumcash_div_cash(PG_FUNCTION_ARGS);
4041

4142
externDatumcash_mul_flt8(PG_FUNCTION_ARGS);
4243
externDatumflt8_mul_cash(PG_FUNCTION_ARGS);
@@ -63,4 +64,7 @@ extern Datum cashsmaller(PG_FUNCTION_ARGS);
6364

6465
externDatumcash_words(PG_FUNCTION_ARGS);
6566

67+
externDatumcash_numeric(PG_FUNCTION_ARGS);
68+
externDatumnumeric_cash(PG_FUNCTION_ARGS);
69+
6670
#endif/* CASH_H */

0 commit comments

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