To: postgresql@novacolor.ca
Cc: pgsql-sql@postgresql.org
Subject: Re: create function atof? 
In-reply-to: <200402200904.11199.postgresql@novacolor.ca> 
References: <200402200904.11199.postgresql@novacolor.ca>
Comments: In-reply-to mark <postgresql@novacolor.ca>
	message dated "Fri, 20 Feb 2004 09:04:11 -0500"
Date: Fri, 20 Feb 2004 09:54:14 -0500
Message-ID: <27684.1077288854@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

mark <postgresql@novacolor.ca> writes:
> Is it possible to create a database function that mimics the C function atof?

Just cast.

There doesn't seem to be a pg_cast entry for varchar to float8, but you
could cast to text and then float8, or you could use functional notation
for the cast (which is a tad more forgiving than CAST or ::).

regression=# select '1234.5'::varchar::float8;
ERROR:  cannot cast type character varying to double precision
regression=# select '1234.5'::text::float8;
 float8
--------
 1234.5
(1 row)

regression=# select float8('1234.5'::varchar);
 float8
--------
 1234.5
(1 row)

Or write a plpgsql function that simply tries to return its input.
I believe that whenever plpgsql is called on to make a type conversion,
it will invoke the output function of the given type and then the input
function of the other type, so it will work for any cases where the
external textual representation looks the same.

regression=# create function atof(varchar) returns float as
regression-# 'begin
regression'#   return $1;
regression'# end' language plpgsql strict immutable;
CREATE FUNCTION

regression=# select atof('1234.5');
  atof
--------
 1234.5
(1 row)

regression=# select atof('zit');
ERROR:  invalid input syntax for type double precision: "zit"
CONTEXT:  PL/pgSQL function "atof" while casting return value to function's return type

			regards, tom lane