From aca9723db6f8614286a0eb82517e29407b09193e Mon Sep 17 00:00:00 2001 From: Joey Adams 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