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

> 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
> 


Tue, 22 Jun 1999 17:39:19 +0000 (AST)
Duane Currie <dcurrie@sandman.acadiau.ca>
Re: [HACKERS] money data type and conversions
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

Tue, 22 Jun 1999 13:40:09 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
money data type and conversions
Bruce,

TODO almost done:

I have three files which implement two functions to convert from 
money to integer and from integer to money.  Tested it out... works

Who should I send these to to have the changes applied to a later release?

Thanx,
Duane


> > Thus spake Bruce Momjian
> > > Can someone explain why our money type in 6.5 requires quotes, and why
> > > there is no int() function for it?
> > 
> > Good question.  I wonder if #2 is the answer to #1.
> > 
> 
> Added to TODO:
> 
>   * Money type requires quotes for input, and no coversion functions
> 
> -- 
>   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
> 


Tue, 22 Jun 1999 17:03:56 -0300 (ADT)
Duane Currie <dcurrie@sandman.acadiau.ca>
Re: [HACKERS] money data type and conversions

On 22-Jun-99 Bruce Momjian wrote:
> Can someone explain why our money type in 6.5 requires quotes, and why
> there is no int() function for it?

Dunno about the int() stuff, but it seems that I've always had to quote
money.  I ass-u-me d  it had to do with the $ sign, 'cuze using a float
would cause it to crab about the wrong data type.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================



Tue, 22 Jun 1999 17:02:03 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
RE: [HACKERS] money data type and conversions
Thus spake Bruce Momjian
> Can someone explain why our money type in 6.5 requires quotes, and why
> there is no int() function for it?

Good question.  I wonder if #2 is the answer to #1.

-- 
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.

Tue, 22 Jun 1999 17:14:48 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] money data type and conversions
> Thus spake Bruce Momjian
> > Can someone explain why our money type in 6.5 requires quotes, and why
> > there is no int() function for it?
> 
> Good question.  I wonder if #2 is the answer to #1.
> 

Added to TODO:

  * Money type requires quotes for input, and no coversion functions

-- 
  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

Tue, 22 Jun 1999 18:06:19 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] money data type and conversions
> Bruce,
> 
> TODO almost done:
> 
> I have three files which implement two functions to convert from 
> money to integer and from integer to money.  Tested it out... works
> 
> Who should I send these to to have the changes applied to a later release?

Send them over to the patches list.  We will apply them to 6.6 because
they will require a dump/restore.  Thomas will probably do something
with them and binary compatible types.

-- 
  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

Tue, 22 Jun 1999 20:29:01 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] money data type and conversions
> > > Can someone explain why our money type in 6.5 requires quotes, and why
> > > there is no int() function for it?
> > Good question.  I wonder if #2 is the answer to #1.
> Added to TODO:
>   * Money type requires quotes for input, and no coversion functions

And while you are at it, add one more entry:

    * Remove money type

NUMERIC and DECIMAL are (or should be, if there are rough edges since
they are so new) are the SQL92-way to represent currency. And, they
are compatible with all different conventions, since you can set the
decimal place and size of the fractional part as you want.

We didn't remove the money type for v6.5 since the newer types are so,
uh, new. But if there are no reported, unfixable problems we should
drop the money type for the next release.

As a sop to make the conversion easier, we can equivalence "money" to
"numeric(xx,2)" at that time.

                  - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Wed, 23 Jun 1999 01:52:20 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] money data type and conversions
> > > > Can someone explain why our money type in 6.5 requires quotes, and why
> > > > there is no int() function for it?
> > > Good question.  I wonder if #2 is the answer to #1.
> > Added to TODO:
> >   * Money type requires quotes for input, and no coversion functions
> 
> And while you are at it, add one more entry:
> 
>     * Remove money type

Added to TODO:

	* Remove Money type and make synonym for decimal(x,2)

What about the printing of currency symbol?


-- 
  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

Tue, 22 Jun 1999 21:58:06 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] money data type and conversions
> What about the printing of currency symbol?

Won't be missed, at least for anyone writing to SQL92 ;)

                 - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Wed, 23 Jun 1999 02:04:59 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] money data type and conversions

For people wondering what BeOS is:

	http://www.be.com/aboutbe/index.html

Seems it is an OS developed for digital media and network appliances.

-- 
  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

Tue, 22 Jun 1999 22:16:03 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
BeOS
Thus spake Bruce Momjian
> 	* Remove Money type and make synonym for decimal(x,2)
> 
> What about the printing of currency symbol?

That's the one thing that the new types don't offer but that was often
problematical anyway.  In fact, I even submitted a patch to cash.c to
remove the currency symbol based on earlier discussions.  The only
reason it wasn't added was that the type was supposed to be removed
soon anyway.  Perhaps we should apply the patch anyway for now until
it is removed.

-- 
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.

Tue, 22 Jun 1999 23:24:24 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] money data type and conversions
> Thus spake Bruce Momjian
> > 	* Remove Money type and make synonym for decimal(x,2)
> > 
> > What about the printing of currency symbol?
> 
> That's the one thing that the new types don't offer but that was often
> problematical anyway.  In fact, I even submitted a patch to cash.c to
> remove the currency symbol based on earlier discussions.  The only
> reason it wasn't added was that the type was supposed to be removed
> soon anyway.  Perhaps we should apply the patch anyway for now until
> it is removed.
> 

Not good to change behavour in a minor release if we can help it.

-- 
  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

Tue, 22 Jun 1999 23:27:20 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] money data type and conversions
At 01:52 AM 6/23/99 +0000, Thomas Lockhart wrote:

>NUMERIC and DECIMAL are (or should be, if there are rough edges since
>they are so new) are the SQL92-way to represent currency. And, they
>are compatible with all different conventions, since you can set the
>decimal place and size of the fractional part as you want.

This is an excellent point.  The portable and standard numeric
and decimal types are the way to go.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, and other goodies at
  http://donb.photo.net

Tue, 22 Jun 1999 22:44:20 -0700
Don Baccus <dhogaza@pacifier.com>
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, 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

Wed, 23 Jun 1999 11:53:44 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] money data type and conversions
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.

Wed, 23 Jun 1999 15:24:18 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] money data type and conversions
> 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 19026

Wed, 23 Jun 1999 15:53:10 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] money data type and conversions]
Thus 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.

Wed, 23 Jun 1999 16:54:12 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] money data type and conversions]
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

Wed, 23 Jun 1999 19:03:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] money data type and conversions] 
> 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

Thu, 24 Jun 1999 11:34:53 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] money data type and conversions]