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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers