Re: How to CREATE FUNCTION into PostgreSQL

From: Bill Moran <wmoran(at)potentialtech(dot)com>
To:
Cc: Marcos Cruz <Marcos(dot)Cruz(at)cenpra(dot)gov(dot)br>, postgres <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to CREATE FUNCTION into PostgreSQL
Date: 2004-03-16 18:53:48
Message-ID: 40574D3C.6080208@potentialtech.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Valter 2004-03-16 19:20:15 Network Connection
Previous Message Bill Moran 2004-03-16 18:36:10 L doesn't seem to be working in to_char()