Re: Casting Varchar to Numeric

Martijn van Oosterhout wrote:
> On Wed, Nov 28, 2001 at 04:30:00PM -0000, Andy Marden wrote:
> > Well, I've finally sorted it out and can now convert text/varchar fields
> > into numeric I cannot BELIEVE I've had to resort to such things.
> >
> > I've changed all my varchar fields to text and then applied this to them
> > (column name is 'litre_amount'):
>
> <snip ugly conversion method>
>
> Does this work?
>
> select litre_amount::float::numeric;

Maybe it works, but with the step through float you loose
precision. In the old days where the type input/output
functions wheren't protected, one was able to use

select numeric_in(textout(litre_amount)) from ...

Well, some thought it'd not be such a good idea to let end
users muck around with C string pointers, and IIRC I was one
of them.

But there are still the internal casting capabilities of
PL/pgSQL. What about

CREATE FUNCTION to_numeric ( text ) RETURNS numeric AS '
BEGIN
RETURN $1;
END;' LANGUAGE 'plpgsql';

Maybe this function is far too overcomplicated and someone
might enhance the algorithm :-)

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com