Re: [HACKERS] money data type and conversions
Well, can't explain the why's...
But I have the code to add to cash.c and cash.h to add the conversion
functions, but still have to figure out how to get PostgreSQL to recognize
it... Guessing... it's in fmgrtab.c right?
Duane
Show quoted text
Can someone explain why our money type in 6.5 requires quotes, and why
there is no int() function for it?---------------------------------------------------------------------------
test=> create table t(x money);
CREATE
test=> insert into t values (3.3);
ERROR: Attribute 'x' is of type 'money' but expression is of type 'float8'
You will need to rewrite or cast the expression
test=> insert into t values (3.33);
ERROR: Attribute 'x' is of type 'money' but expression is of type 'float8'
You will need to rewrite or cast the expression
test=> insert into t values (money(3.33));
ERROR: No such function 'money' with the specified attributes
test=> insert into t values (cash(3.33));
ERROR: No such function 'cash' with the specified attributes
test=> insert into t values (3.33);
ERROR: Attribute 'x' is of type 'money' but expression is of type 'float8'
You will need to rewrite or cast the expression
test=> insert into t values ('3.33');
INSERT 18569 1
test=> select int(x) from t;
ERROR: No such function 'int' with the specified attributes
test=> select int4(x) from t;
ERROR: No such function 'int4' with the specified attributes-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Import Notes
Reply to msg id not found: 199906221740.NAA21057@candle.pha.pa.us
Well, can't explain the why's...
But I have the code to add to cash.c and cash.h to add the conversion
functions, but still have to figure out how to get PostgreSQL to recognize
it... Guessing... it's in fmgrtab.c right?
Duane, sonds like people want to remove the Money/cash type and transfer
everyone over to decimal which has full precision and is much better for
currency.
Sorry.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Thus spake Bruce Momjian
Well, can't explain the why's...
But I have the code to add to cash.c and cash.h to add the conversion
functions, but still have to figure out how to get PostgreSQL to recognize
it... Guessing... it's in fmgrtab.c right?Duane, sonds like people want to remove the Money/cash type and transfer
everyone over to decimal which has full precision and is much better for
currency.
Is there any reason why we don't just leave money in? I know that NUMERIC
and DECIMAL will handle money amounts but the money type does a few
extra things related to locale, even if we remove the currency symbol
and perhaps we should leave that in if people are expected to use the
new types. It also determines whether the comma or period is the correct
separator, puts separators in the correct place and determines where the
decimal point goes. Also, check out what the following does.
select cash_words_out('157.23');
Althugh there appears to be a bug in that function that chops the last
character from the output.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.Thus spake Bruce Momjian
Well, can't explain the why's...
But I have the code to add to cash.c and cash.h to add the conversion
functions, but still have to figure out how to get PostgreSQL to recognize
it... Guessing... it's in fmgrtab.c right?Duane, sonds like people want to remove the Money/cash type and transfer
everyone over to decimal which has full precision and is much better for
currency.Is there any reason why we don't just leave money in? I know that NUMERIC
and DECIMAL will handle money amounts but the money type does a few
extra things related to locale, even if we remove the currency symbol
and perhaps we should leave that in if people are expected to use the
new types. It also determines whether the comma or period is the correct
separator, puts separators in the correct place and determines where the
decimal point goes. Also, check out what the following does.select cash_words_out('157.23');
Althugh there appears to be a bug in that function that chops the last
character from the output.
Maybe we will have to add '$' symbols to a special case of the numeric
type, or add a function to output numeric in money format?
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Thus spake Bruce Momjian
Maybe we will have to add '$' symbols to a special case of the numeric
type, or add a function to output numeric in money format?
That's another thought I had. However, it isn't the '$' symbol. The
idea is that it takes the symbol from the current locale. That's what
makes handling the information so hard, you don't know how many characters
are used by the currency symbol.
However, cash_out and cash_words_out can probably be dropped into the
decimal code. There should be some small changes though. In particular
the money type moves the decimal point to a position in a fixed string
of digits but for decimal it should honour the type's positioning.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
Bruce Momjian <maillist@candle.pha.pa.us> writes:
Maybe we will have to add '$' symbols to a special case of the numeric
type, or add a function to output numeric in money format?
I like the last idea (add a formatting function), because it's simple,
self-contained, and doesn't force any solutions on anyone. Don't want
any decoration on your number? Just read it out. Don't like the
decoration added by the formatting function? Write your own function.
No table reconstruction required. With a data-type-driven approach,
changing your mind is painful because you have to rebuild your tables.
We'd probably also want an inverse function that would strip off the
decoration and produce a numeric, but that's easy too...
regards, tom lane
Import Notes
Reply to msg id not found: YourmessageofWed23Jun1999155310-0400199906231953.PAA20660@candle.pha.pa.us | Resolved by subject fallback
Bruce Momjian <maillist@candle.pha.pa.us> writes:
Maybe we will have to add '$' symbols to a special case of the numeric
type, or add a function to output numeric in money format?I like the last idea (add a formatting function), because it's simple,
self-contained, and doesn't force any solutions on anyone. Don't want
any decoration on your number? Just read it out. Don't like the
decoration added by the formatting function? Write your own function.
No table reconstruction required. With a data-type-driven approach,
changing your mind is painful because you have to rebuild your tables.We'd probably also want an inverse function that would strip off the
decoration and produce a numeric, but that's easy too...
Added to TODO:
* Remove Money type, add money formatting for decimal type
I should add I have reorganized the TODO list to be clearer. People may
want to check it out on our newly designed web site.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026