Attached is an updated version of the patch to allow conversion of
int4/int8 directly to money. I added overflow checks, dropped
int2->cash, and updated the documentation.
- Joey
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index ecc79e2..13b888d 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -886,15 +886,22 @@ ALTER SEQUENCE <replaceable class="parameter">tablename</replaceable>_<replaceab
</para>
<para>
- Values of the <type>numeric</type> data type can be cast to
- <type>money</type>. Other numeric types can be converted to
- <type>money</type> by casting to <type>numeric</type> first, for example:
+ Values of the <type>numeric</type>, <type>int</type>, and
+ <type>bigint</type> data types can be cast to <type>money</type>.
+ Conversion from the <type>real</type> and <type>double precision</type>
+ data types can be done by casting to <type>numeric</type> first, for
+ example:
<programlisting>
-SELECT 1234::numeric::money;
+SELECT '12.34'::float8::numeric::money;
</programlisting>
+ However, this is not recommended. Floating point numbers should not be
+ used to handle money due to the potential for rounding errors.
+ </para>
+
+ <para>
A <type>money</type> value can be cast to <type>numeric</type> without
loss of precision. Conversion to other types could potentially lose
- precision, and it must be done in two stages, for example:
+ precision, and must also be done in two stages:
<programlisting>
SELECT '52093.89'::money::numeric::float8;
</programlisting>
diff --git a/src/backend/utils/adt/cash.c b/src/backend/utils/adt/cash.c
index 67f5128..61f7370 100644
--- a/src/backend/utils/adt/cash.c
+++ b/src/backend/utils/adt/cash.c
@@ -92,6 +92,21 @@ num_word(Cash value)
return buf;
} /* num_word() */
+static bool
+int8mul_direct(int64 arg1, int64 arg2, int64 *result)
+{
+ *result = arg1 * arg2;
+
+ /* Overflow check. See comment in int8mul */
+ if (arg1 != (int64) ((int32) arg1) || arg2 != (int64) ((int32) arg2))
+ {
+ if (arg2 != 0 &&
+ (*result / arg2 != arg1 || (arg2 == -1 && arg1 < 0 && *result < 0)))
+ return false;
+ }
+
+ return true;
+}
/* cash_in()
* Convert a string to a cash data type.
@@ -938,3 +953,67 @@ numeric_cash(PG_FUNCTION_ARGS)
PG_RETURN_CASH(result);
}
+
+/* int4_cash()
+ * Convert int4 (int) to cash
+ */
+Datum
+int4_cash(PG_FUNCTION_ARGS)
+{
+ int32 amount = PG_GETARG_INT32(0);
+ Cash result;
+ int fpoint;
+ int64 scale;
+ int i;
+ struct lconv *lconvert = PGLC_localeconv();
+
+ /* see comments about frac_digits in cash_in() */
+ fpoint = lconvert->frac_digits;
+ if (fpoint < 0 || fpoint > 10)
+ fpoint = 2;
+
+ /* compute required scale factor */
+ scale = 1;
+ for (i = 0; i < fpoint; i++)
+ scale *= 10;
+
+ /* compute amount * scale, checking for overflow */
+ if (!int8mul_direct(amount, scale, &result))
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("money out of range")));
+
+ PG_RETURN_CASH(result);
+}
+
+/* int8_cash()
+ * Convert int8 (bigint) to cash
+ */
+Datum
+int8_cash(PG_FUNCTION_ARGS)
+{
+ int64 amount = PG_GETARG_INT64(0);
+ Cash result;
+ int fpoint;
+ int64 scale;
+ int i;
+ struct lconv *lconvert = PGLC_localeconv();
+
+ /* see comments about frac_digits in cash_in() */
+ fpoint = lconvert->frac_digits;
+ if (fpoint < 0 || fpoint > 10)
+ fpoint = 2;
+
+ /* compute required scale factor */
+ scale = 1;
+ for (i = 0; i < fpoint; i++)
+ scale *= 10;
+
+ /* compute amount * scale, checking for overflow */
+ if (!int8mul_direct(amount, scale, &result))
+ ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("money out of range")));
+
+ PG_RETURN_CASH(result);
+}
diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h
index bf8a6fc..f7b7b76 100644
--- a/src/include/catalog/pg_cast.h
+++ b/src/include/catalog/pg_cast.h
@@ -126,6 +126,8 @@ DATA(insert ( 1700 700 1745 i f ));
DATA(insert ( 1700 701 1746 i f ));
DATA(insert ( 790 1700 3823 a f ));
DATA(insert ( 1700 790 3824 a f ));
+DATA(insert ( 23 790 3811 a f ));
+DATA(insert ( 20 790 3812 a f ));
/* Allow explicit coercions between int4 and bool */
DATA(insert ( 23 16 2557 e f ));
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index cff64ba..7919a40 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -971,6 +971,10 @@ DATA(insert OID = 3823 ( numeric PGNSP PGUID 12 1 0 0 f f f t f s 1 0 1700
DESCR("convert money to numeric");
DATA(insert OID = 3824 ( money PGNSP PGUID 12 1 0 0 f f f t f s 1 0 790 "1700" _null_ _null_ _null_ _null_ numeric_cash _null_ _null_ _null_ ));
DESCR("convert numeric to money");
+DATA(insert OID = 3811 ( money PGNSP PGUID 12 1 0 0 f f f t f s 1 0 790 "23" _null_ _null_ _null_ _null_ int4_cash _null_ _null_ _null_ ));
+DESCR("convert int4 to money");
+DATA(insert OID = 3812 ( money PGNSP PGUID 12 1 0 0 f f f t f s 1 0 790 "20" _null_ _null_ _null_ _null_ int8_cash _null_ _null_ _null_ ));
+DESCR("convert int8 to money");
/* OIDS 900 - 999 */
diff --git a/src/include/utils/cash.h b/src/include/utils/cash.h
index 81b51ad..3a491f9 100644
--- a/src/include/utils/cash.h
+++ b/src/include/utils/cash.h
@@ -67,4 +67,7 @@ extern Datum cash_words(PG_FUNCTION_ARGS);
extern Datum cash_numeric(PG_FUNCTION_ARGS);
extern Datum numeric_cash(PG_FUNCTION_ARGS);
+extern Datum int4_cash(PG_FUNCTION_ARGS);
+extern Datum int8_cash(PG_FUNCTION_ARGS);
+
#endif /* CASH_H */
diff --git a/src/test/regress/expected/money.out b/src/test/regress/expected/money.out
index ec4169c..0b1f39c 100644
--- a/src/test/regress/expected/money.out
+++ b/src/test/regress/expected/money.out
@@ -185,3 +185,66 @@ SELECT * FROM money_data;
$123.46
(1 row)
+-- Cast int4/int8 to money
+SELECT 1234567890::money;
+ money
+-------------------
+ $1,234,567,890.00
+(1 row)
+
+SELECT 12345678901234567::money;
+ money
+----------------------------
+ $12,345,678,901,234,567.00
+(1 row)
+
+SELECT 123456789012345678::money;
+ERROR: money out of range
+SELECT 9223372036854775807::money;
+ERROR: money out of range
+SELECT (-12345)::money;
+ money
+-------------
+ -$12,345.00
+(1 row)
+
+SELECT (-1234567890)::money;
+ money
+--------------------
+ -$1,234,567,890.00
+(1 row)
+
+SELECT (-12345678901234567)::money;
+ money
+-----------------------------
+ -$12,345,678,901,234,567.00
+(1 row)
+
+SELECT (-123456789012345678)::money;
+ERROR: money out of range
+SELECT (-9223372036854775808)::money;
+ERROR: money out of range
+SELECT 1234567890::int4::money;
+ money
+-------------------
+ $1,234,567,890.00
+(1 row)
+
+SELECT 12345678901234567::int8::money;
+ money
+----------------------------
+ $12,345,678,901,234,567.00
+(1 row)
+
+SELECT (-1234567890)::int4::money;
+ money
+--------------------
+ -$1,234,567,890.00
+(1 row)
+
+SELECT (-12345678901234567)::int8::money;
+ money
+-----------------------------
+ -$12,345,678,901,234,567.00
+(1 row)
+
diff --git a/src/test/regress/sql/money.sql b/src/test/regress/sql/money.sql
index 580425e..09b9476 100644
--- a/src/test/regress/sql/money.sql
+++ b/src/test/regress/sql/money.sql
@@ -56,3 +56,18 @@ SELECT * FROM money_data;
DELETE FROM money_data;
INSERT INTO money_data VALUES ('$123.459');
SELECT * FROM money_data;
+
+-- Cast int4/int8 to money
+SELECT 1234567890::money;
+SELECT 12345678901234567::money;
+SELECT 123456789012345678::money;
+SELECT 9223372036854775807::money;
+SELECT (-12345)::money;
+SELECT (-1234567890)::money;
+SELECT (-12345678901234567)::money;
+SELECT (-123456789012345678)::money;
+SELECT (-9223372036854775808)::money;
+SELECT 1234567890::int4::money;
+SELECT 12345678901234567::int8::money;
+SELECT (-1234567890)::int4::money;
+SELECT (-12345678901234567)::int8::money;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
