Re: How to CREATE FUNCTION into PostgreSQL - Mailing list pgsql-general

Marcos Cruz wrote:
> Hi,
>
> I need to create a function to convert a string with a portuguese float-point
> representation ( thousand sep = dot, decimal point = comma) to a float8.
>
> When I execute the SQL:
> SELECT replace(replace ('1.234.567,08', '.', ''), ',' , '.')::float8 AS test;
>
> the result was the number 1234567.08, which may insert
> as a float8 field of a table
>
> When I issue the SQL:
> CREATE FUNCTION to_double(text) RETURNS float8 AS
> 'SELECT (replace(replace(text, '.' , '') , ',' , '.')::float8 AS RESULT
> LANGUAGE SQL
>
> it generates an error and function isn't created.

In the future, you'll make it easier on folks who want to help by stating
exactly what the error says.

However, I think I can guess the problem.  You need to escape the ' within
the function definition.  Specifically, try this:

CREATE FUNCTION to_double(text) RETURNS float8 AS
'SELECT (replace(replace(text, ''.'' , '''') , '','' , ''.'')::float8 AS RESULT
LANGUAGE SQL

--
Bill Moran
Potential Technologies
http://www.potentialtech.com