On Thu, Mar 31, 2011 at 6:39 PM, Stephen Frost <sfr...@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
From aca9723db6f8614286a0eb82517e29407b09193e Mon Sep 17 00:00:00 2001
From: Joey Adams <joeyadams3.14...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
