Re: Inserting Money Types

Greg Lindstrom <greg.lindstrom@novasyshealth.com> writes:
> I am running postgres 8.0.8 on a Gento system and am having trouble updating a column of type money (yes, I know it's
depricatedbut I have to work with an existing database).  When we do the initial INSERT statement and pass in a float
itworks fine, but I am writing a routine that takes values from a varchar field of another table and attempts to update
themoney field and I am told I need to cast it.  When I attempt to cast it I'm told that I can't cast a char to money,
floatto money, or numeric to money!   

There don't seem to be any built-in casts to money:

regression=# select * from pg_cast where casttarget = 'money'::regtype;
 castsource | casttarget | castfunc | castcontext
------------+------------+----------+-------------
(0 rows)

However, you can make your own out of spare parts.  plpgsql is good for
this because it's willing to convert anything to anything else as long
as their textual representations are compatible.  So:

regression=# select '123.45'::varchar::money;
ERROR:  cannot cast type character varying to money
LINE 1: select '123.45'::varchar::money;
                                  ^
regression=# create function money(varchar) returns money as $$
regression$# begin
regression$#   return $1;
regression$# end$$ language plpgsql strict immutable;
CREATE FUNCTION
regression=# create cast(varchar as money) with function money(varchar);
CREATE CAST
regression=# select '123.45'::varchar::money;
  money
---------
 $123.45
(1 row)

Or just create the conversion function and invoke it explicitly.  If you
need to do any massaging of the varchar string (ie, it's not already
valid input for type money) then you probably just want to use a
function to do it instead of pretending that it's a general-purpose
cast.

            regards, tom lane