Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to CREATE FUNCTION into PostgreSQL

How to CREATE FUNCTION into PostgreSQL

From
Marcos Cruz
Date:
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.

Someone can help me?

Thaks in advance,
Marcos

Re: How to CREATE FUNCTION into PostgreSQL

From
Bill Moran
Date:
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

Re: How to CREATE FUNCTION into PostgreSQL

From
Bill Moran
Date:
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