Re: How to CREATE FUNCTION into PostgreSQL

Bill Moran wrote:
> 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:

Oops ... replying to my own reply, but I also noticed you were missing the
ending quote. You're also using "text" as if it's a variable, it's not.
Try this:

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

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