cast from integer to money
On the open items list, we have:
conversion from integer literals to money type
http://archives.postgresql.org/pgsql-testers/2011-01/msg00000.php
What this is really complaining about is that we added a cast from
numeric to money, but not from integer to money. This isn't really a
bug: the fact that we added one cast doesn't oblige us to add two. On
the other hand, the change is probably harmless and straightforward,
and might reduce user confusion. Right now:
rhaas=# select 1::money;
ERROR: cannot cast type integer to money
LINE 1: select 1::money;
^
rhaas=# select 1.0::money;
money
-------
$1.00
(1 row)
Does anyone care enough about this to put in the effort to fix it, or
should we just let it go? Does anyone see a reason why we wouldn't
want to do this, if someone's motivated to code it up?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> wrote:
On the open items list, we have:
conversion from integer literals to money type
http://archives.postgresql.org/pgsql-testers/2011-01/msg00000.phpWhat this is really complaining about is that we added a cast from
numeric to money, but not from integer to money. This isn't
really a bug: the fact that we added one cast doesn't oblige us to
add two. On the other hand, the change is probably harmless and
straightforward, and might reduce user confusion.
There were reasonable arguments made why this could be a bad idea --
primarily around the question of whether '395' represented $3.95 or
$395.00. Going the other way has issues with truncation of
fractions and the number of digits which can be handled. I'm not
convinced it's sane, and I feel strongly it's too late in the cycle
to try to implement.
-Kevin
On Thu, Mar 31, 2011 at 4:58 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
Robert Haas <robertmhaas@gmail.com> wrote:
On the open items list, we have:
conversion from integer literals to money type
http://archives.postgresql.org/pgsql-testers/2011-01/msg00000.phpWhat this is really complaining about is that we added a cast from
numeric to money, but not from integer to money. This isn't
really a bug: the fact that we added one cast doesn't oblige us to
add two. On the other hand, the change is probably harmless and
straightforward, and might reduce user confusion.There were reasonable arguments made why this could be a bad idea --
primarily around the question of whether '395' represented $3.95 or
$395.00.
That's not too hard to figure out, right? If 1.00 means $1.00, 1 had
better not mean $0.01, or there will be riots in the streets.
Going the other way has issues with truncation of
fractions and the number of digits which can be handled.
Notice I didn't propose that.
I'm not
convinced it's sane, and I feel strongly it's too late in the cycle
to try to implement.
Fair enough. Any contrary votes?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> wrote:
There were reasonable arguments made why this could be a bad idea
-- primarily around the question of whether '395' represented
$3.95 or $395.00.That's not too hard to figure out, right? If 1.00 means $1.00, 1
had better not mean $0.01, or there will be riots in the streets.Going the other way has issues with truncation of
fractions and the number of digits which can be handled.Notice I didn't propose that.
If you're just talking about going in the one direction, I might be
persuaded that's sane, especially because of the case of literals,
and especially since there are currencies where fractional amounts
aren't used in the conventional representation.
-Kevin
* Kevin Grittner (Kevin.Grittner@wicourts.gov) wrote:
If you're just talking about going in the one direction, I might be
persuaded that's sane, especially because of the case of literals,
and especially since there are currencies where fractional amounts
aren't used in the conventional representation.
Going just integer->money, with the "1" -> "$1.00", seems completely
reasonable to me. As for being too late in the cycle.. if someone's
willing to do the work, I can't imagine it breaking anything, so I
wouldn't be against putting it in. It really should be before the
first beta tho.
Thanks,
Stephen
On Mar 31, 2011, at 6:39 PM, Stephen Frost <sfrost@snowman.net> wrote:
* Kevin Grittner (Kevin.Grittner@wicourts.gov) wrote:
If you're just talking about going in the one direction, I might be
persuaded that's sane, especially because of the case of literals,
and especially since there are currencies where fractional amounts
aren't used in the conventional representation.Going just integer->money, with the "1" -> "$1.00", seems completely
reasonable to me. As for being too late in the cycle.. if someone's
willing to do the work, I can't imagine it breaking anything, so I
wouldn't be against putting it in. It really should be before the
first beta tho.
Agreed, emphatically.
...Robert
On Thu, Mar 31, 2011 at 6:39 PM, Stephen Frost <sfrost@snowman.net> wrote:
Going just integer->money, with the "1" -> "$1.00", seems completely
reasonable to me. As for being too late in the cycle.. if someone's
willing to do the work, I can't imagine it breaking anything, so I
wouldn't be against putting it in. It really should be before the
first beta tho.
Attached is a patch which enables casting int2/int4/int8 to money,
with the same scaling as numeric uses. Hence, 1::money yields '$1.00'
. The only other numeric types (other than oid, cardinal_number,
etc.) that can't be casted directly to money are float4 and float8,
and I suspect this is intentional.
The patch includes tests, but does not update the documentation.
Should the docs be updated where it reads "Values of the numeric data
type can be cast to money. Other numeric types can be converted to
money by casting to numeric first" ?
Because this change adds rows to the pg_proc and pg_cast catalogs,
applying this patch for 9.1 will require alpha users to initdb again.
Is that acceptable?
Regards,
Joey Adams
Attachments:
cast-integer-to-money.patchtext/x-patch; charset=US-ASCII; name=cast-integer-to-money.patchDownload
From aca9723db6f8614286a0eb82517e29407b09193e Mon Sep 17 00:00:00 2001
From: Joey Adams <joeyadams3.14159@gmail.com>
Date: Fri, 1 Apr 2011 22:09:12 -0400
Subject: [PATCH] Allow cast from int2/int4/int8 to money
---
src/backend/utils/adt/cash.c | 75 +++++++++++++++++++++++++++++++++++
src/include/catalog/pg_cast.h | 3 +
src/include/catalog/pg_proc.h | 6 +++
src/include/utils/cash.h | 4 ++
src/test/regress/expected/money.out | 73 ++++++++++++++++++++++++++++++++++
src/test/regress/sql/money.sql | 14 ++++++
6 files changed, 175 insertions(+), 0 deletions(-)
diff --git a/src/backend/utils/adt/cash.c b/src/backend/utils/adt/cash.c
index 67f5128..1981123 100644
--- a/src/backend/utils/adt/cash.c
+++ b/src/backend/utils/adt/cash.c
@@ -938,3 +938,78 @@ numeric_cash(PG_FUNCTION_ARGS)
PG_RETURN_CASH(result);
}
+
+/* int2_cash()
+ * Convert int2 (smallint) to cash
+ */
+Datum
+int2_cash(PG_FUNCTION_ARGS)
+{
+ int16 amount = PG_GETARG_INT16(0);
+ Cash result;
+ int fpoint;
+ 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;
+
+ /* result = amount * 10^fpoint */
+ result = amount;
+ for (i = 0; i < fpoint; i++)
+ result *= 10;
+
+ 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;
+ 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;
+
+ /* result = amount * 10^fpoint */
+ result = amount;
+ for (i = 0; i < fpoint; i++)
+ result *= 10;
+
+ 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;
+ 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;
+
+ /* result = amount * 10^fpoint */
+ result = amount;
+ for (i = 0; i < fpoint; i++)
+ result *= 10;
+
+ PG_RETURN_CASH(result);
+}
diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h
index bf8a6fc..5c2bbef 100644
--- a/src/include/catalog/pg_cast.h
+++ b/src/include/catalog/pg_cast.h
@@ -126,6 +126,9 @@ 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 ( 21 790 3825 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..1f46fd9 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -971,6 +971,12 @@ 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 = 3825 ( money PGNSP PGUID 12 1 0 0 f f f t f s 1 0 790 "21" _null_ _null_ _null_ _null_ int2_cash _null_ _null_ _null_ ));
+DESCR("convert int2 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..5d4b525 100644
--- a/src/include/utils/cash.h
+++ b/src/include/utils/cash.h
@@ -67,4 +67,8 @@ extern Datum cash_words(PG_FUNCTION_ARGS);
extern Datum cash_numeric(PG_FUNCTION_ARGS);
extern Datum numeric_cash(PG_FUNCTION_ARGS);
+extern Datum int2_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..642e7af 100644
--- a/src/test/regress/expected/money.out
+++ b/src/test/regress/expected/money.out
@@ -185,3 +185,76 @@ SELECT * FROM money_data;
$123.46
(1 row)
+-- Cast int2/int4/int8 to money
+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 (-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 12345::int2::money;
+ money
+------------
+ $12,345.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)
+
+SELECT (-12345)::int2::money;
+ money
+-------------
+ -$12,345.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..d421896 100644
--- a/src/test/regress/sql/money.sql
+++ b/src/test/regress/sql/money.sql
@@ -56,3 +56,17 @@ SELECT * FROM money_data;
DELETE FROM money_data;
INSERT INTO money_data VALUES ('$123.459');
SELECT * FROM money_data;
+
+-- Cast int2/int4/int8 to money
+SELECT 12345::money;
+SELECT 1234567890::money;
+SELECT 12345678901234567::money;
+SELECT (-12345)::money;
+SELECT (-1234567890)::money;
+SELECT (-12345678901234567)::money;
+SELECT 12345::int2::money;
+SELECT 1234567890::int4::money;
+SELECT 12345678901234567::int8::money;
+SELECT (-12345)::int2::money;
+SELECT (-1234567890)::int4::money;
+SELECT (-12345678901234567)::int8::money;
--
1.7.1
On Fri, Apr 1, 2011 at 10:33 PM, Joseph Adams
<joeyadams3.14159@gmail.com> wrote:
On Thu, Mar 31, 2011 at 6:39 PM, Stephen Frost <sfrost@snowman.net> wrote:
Going just integer->money, with the "1" -> "$1.00", seems completely
reasonable to me. As for being too late in the cycle.. if someone's
willing to do the work, I can't imagine it breaking anything, so I
wouldn't be against putting it in. It really should be before the
first beta tho.Attached is a patch which enables casting int2/int4/int8 to money,
with the same scaling as numeric uses. Hence, 1::money yields '$1.00'
Thanks for the patch, but I think you forgot to worry about overflow:
rhaas=# select 9223372036854775807::money;
money
--------
-$1.00
(1 row)
. The only other numeric types (other than oid, cardinal_number,
etc.) that can't be casted directly to money are float4 and float8,
and I suspect this is intentional.
Agreed.
The patch includes tests, but does not update the documentation.
Should the docs be updated where it reads "Values of the numeric data
type can be cast to money. Other numeric types can be converted to
money by casting to numeric first" ?
Yes.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On Fri, Apr 1, 2011 at 10:33 PM, Joseph Adams
<joeyadams3.14159@gmail.com> wrote:The only other numeric types (other than oid, cardinal_number,
etc.) that can't be casted directly to money are float4 and float8,
and I suspect this is intentional.
Agreed.
BTW, I think inclusion of int2 in this patch is just a waste of code
space. The main argument for supporting these casts seems to be that
integer literals produced by the parser should be castable to money
without special pushups. But the parser never produces native int2
constants. So int4 and int8 will cover all the useful cases.
regards, tom lane
On Sun, Apr 3, 2011 at 11:23 PM, Robert Haas <robertmhaas@gmail.com> wrote:
Thanks for the patch, but I think you forgot to worry about overflow:
rhaas=# select 9223372036854775807::money;
money
--------
-$1.00
(1 row)
cash_in doesn't test for overflow, either (tested on 8.4.0, 9.0.3, and HEAD):
joey=# select '9223372036854775807'::money;
money
--------
-$1.00
(1 row)
Is this a bug?
Detail: unlike cash_in, numeric_cash does check for overflow
(implicitly, through its use of numeric_int8).
Joey Adams
On Apr 4, 2011, at 1:46 AM, Joseph Adams <joeyadams3.14159@gmail.com> wrote:
On Sun, Apr 3, 2011 at 11:23 PM, Robert Haas <robertmhaas@gmail.com> wrote:
Thanks for the patch, but I think you forgot to worry about overflow:
rhaas=# select 9223372036854775807::money;
money
--------
-$1.00
(1 row)cash_in doesn't test for overflow, either (tested on 8.4.0, 9.0.3, and HEAD):
joey=# select '9223372036854775807'::money;
money
--------
-$1.00
(1 row)Is this a bug?
Seems like it. You have to feel sorry for the guy who deposits 9 quintillion dollars and then gets a note from the bank saying his account is overdrawn...
Detail: unlike cash_in, numeric_cash does check for overflow
(implicitly, through its use of numeric_int8).
Yeah.
...Robert
On Mon, April 4, 2011 7:02 am, Robert Haas wrote:
You have to feel sorry for the guy who deposits 9
quintillion dollars and then gets a note from the bank saying his account
is overdrawn...
Not really ...
cheers
andrew
Robert Haas <robertmhaas@gmail.com> writes:
On Apr 4, 2011, at 1:46 AM, Joseph Adams <joeyadams3.14159@gmail.com> wrote:
On Sun, Apr 3, 2011 at 11:23 PM, Robert Haas <robertmhaas@gmail.com> wrote:
Thanks for the patch, but I think you forgot to worry about overflow:
cash_in doesn't test for overflow, either (tested on 8.4.0, 9.0.3, and HEAD):
Is this a bug?
Seems like it. You have to feel sorry for the guy who deposits 9 quintillion dollars and then gets a note from the bank saying his account is overdrawn...
I'm fairly sure that *none* of the money operations bother to check for
overflow; not only input, but arithmetic. That falls somewhere between
bug and missing feature. It's probably worth fixing but seems outside
the scope of the current patch.
In the meantime, I'm not sure whether the newly added functions should
be held to a higher standard than the existing ones. It might be better
to leave it be, and plan to fix them all at once in a consistent style.
regards, tom lane
On Mon, Apr 4, 2011 at 10:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Apr 4, 2011, at 1:46 AM, Joseph Adams <joeyadams3.14159@gmail.com> wrote:
On Sun, Apr 3, 2011 at 11:23 PM, Robert Haas <robertmhaas@gmail.com> wrote:
Thanks for the patch, but I think you forgot to worry about overflow:
cash_in doesn't test for overflow, either (tested on 8.4.0, 9.0.3, and HEAD):
Is this a bug?Seems like it. You have to feel sorry for the guy who deposits 9 quintillion dollars and then gets a note from the bank saying his account is overdrawn...
I'm fairly sure that *none* of the money operations bother to check for
overflow; not only input, but arithmetic. That falls somewhere between
bug and missing feature. It's probably worth fixing but seems outside
the scope of the current patch.
Oh. Bummer. Yeah, that sounds more like a TODO than an open item.
In the meantime, I'm not sure whether the newly added functions should
be held to a higher standard than the existing ones. It might be better
to leave it be, and plan to fix them all at once in a consistent style.
Maybe. The numeric->money cast does handle it though, so there's at
least some precedent for checking. If you don't want to worry about
it, I'm OK with just putting it in as-is, but I'd probably be inclined
to look for a way to fix it if we can do that without adding too much
complexity.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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
Attachments:
cast-integer-to-money-2.patchtext/x-patch; charset=US-ASCII; name=cast-integer-to-money-2.patchDownload
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;
On Tue, Apr 5, 2011 at 1:10 AM, Joseph Adams <joeyadams3.14159@gmail.com> wrote:
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.
Excellent, thanks.
My only gripe is that I don't think we should duplicate int8mul, so
I've changed your patch to use this incantation:
+ result = DatumGetInt64(DirectFunctionCall2(int8mul, Int64GetDatum(amount
+ Int64GetDatum(scale)));...which is parallel to what the existing numeric -> money cast
already does. That results in a slightly different error message, but
I think that's OK: no one has complained about the numeric -> cash
error message, or the fact that the remaining functions in this module
do no overflow checking at all.
With that change, committed. Thanks for picking this one up.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company