Обсуждение: is_numeric() or extract_numeric() functions?
I can't find an is_numeric() or extract_numeric() function in postgres
so I decided I would make my own. However, when I try to create the
following function:
CREATE OR REPLACE FUNCTION getnumber(varchar(1)) RETURNS integer AS '
BEGIN
return ($1 ~ '[0-9]');
END;
' LANGUAGE 'plpgsql';
I get the following error:
parse error at or near "["
Anyone know how to fix this, or if there is an is_numeric() or
extract_numeric() function available?
Thanks
--
Ron St.Pierre
Syscor R&D
tel: 250-361-1681
email: rstpierre@syscor.com
You forgot to escape the quotes:
CREATE OR REPLACE FUNCTION getnumber(varchar(1)) RETURNS integer AS '
BEGIN
return ($1 ~ ''[0-9]'');
END;
' LANGUAGE 'plpgsql';
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Ron St.Pierre
> Sent: Tuesday, January 28, 2003 5:56 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] is_numeric() or extract_numeric() functions?
>
>
> I can't find an is_numeric() or extract_numeric() function in postgres
> so I decided I would make my own. However, when I try to create the
> following function:
>
>
> CREATE OR REPLACE FUNCTION getnumber(varchar(1)) RETURNS integer AS '
> BEGIN
> return ($1 ~ '[0-9]');
> END;
> ' LANGUAGE 'plpgsql';
>
>
> I get the following error:
>
> parse error at or near "["
>
>
> Anyone know how to fix this, or if there is an is_numeric() or
> extract_numeric() function available?
>
> Thanks
>
> --
> Ron St.Pierre
> Syscor R&D
> tel: 250-361-1681
> email: rstpierre@syscor.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
"Ron St.Pierre" <rstpierre@syscor.com> writes:
> CREATE OR REPLACE FUNCTION getnumber(varchar(1)) RETURNS integer AS '
> BEGIN
> return ($1 ~ '[0-9]');
> END;
> ' LANGUAGE 'plpgsql';
> I get the following error:
> parse error at or near "["
You need to double the quotes in the function body, viz
...
return ($1 ~ ''[0-9]'');
...
Backslash-quote (\') is an alternative way.
regards, tom lane
Ron St.Pierre wrote:
> I can't find an is_numeric() or extract_numeric() function in postgres
> so I decided I would make my own. However, when I try to create the
> following function:
>
>
> CREATE OR REPLACE FUNCTION getnumber(varchar(1)) RETURNS integer AS '
> BEGIN
> return ($1 ~ '[0-9]');
> END;
> ' LANGUAGE 'plpgsql';
>
>
> I get the following error:
>
> parse error at or near "["
>
>
> Anyone know how to fix this, or if there is an is_numeric() or
> extract_numeric() function available?
Your error is caused by including a bare single-quote inside the
function, which is itself single-quoted. You need to escape the single
quote:
CREATE OR REPLACE FUNCTION getnumber(varchar(1)) RETURNS integer AS '
BEGIN
return ($1 ~ ''[0-9]'');
END;
' LANGUAGE 'plpgsql';
That should do the trick.
Zach
Thanks everyone, that worked and I now have a new plpgsql function to extract numbers from a string. I placed it in the PL/pgSQL CookBook (linked from the Users Lounge on the postgres home page -> http://www.brasileiro.net:8080/postgres/cookbook/) if anyone wants it. Tom Lane wrote: >"Ron St.Pierre" <rstpierre@syscor.com> writes: > > >>CREATE OR REPLACE FUNCTION getnumber(varchar(1)) RETURNS integer AS ' >> BEGIN >> return ($1 ~ '[0-9]'); >> END; >>' LANGUAGE 'plpgsql'; >> >> > > > >>I get the following error: >> parse error at or near "[" >> >> > >You need to double the quotes in the function body, viz > > ... > return ($1 ~ ''[0-9]''); > ... > >Backslash-quote (\') is an alternative way. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > -- Ron St.Pierre Syscor R&D tel: 250-361-1681 email: rstpierre@syscor.com
--- "Ron St.Pierre" <rstpierre@syscor.com> wrote: > Thanks everyone, that worked and I now have a new > plpgsql function to > extract numbers from a string. I placed it in the > PL/pgSQL CookBook > (linked from the Users Lounge on the postgres home > page -> > http://www.brasileiro.net:8080/postgres/cookbook/) > if anyone wants it. You mean this _works_ for you??? I doesn't for me, and I don't see how it could. I see a return type mismatch, for starters: $1 ~ ''[0-9]'' will return boolean, whereas your function is defined to return an integer. Testing on my 7.2.1 installation gives this error: jeck=# select getnumber('1'); NOTICE: Error occurred while executing PL/pgSQL function getnumber NOTICE: while casting return value to functions return type ERROR: pg_atoi: error in "t": can't parse "t" > Tom Lane wrote: > > >"Ron St.Pierre" <rstpierre@syscor.com> writes: > > > > > >>CREATE OR REPLACE FUNCTION getnumber(varchar(1)) > RETURNS integer AS ' > >> BEGIN > >> return ($1 ~ '[0-9]'); > >> END; > >>' LANGUAGE 'plpgsql'; > >> > >> > > > > > > > >>I get the following error: > >> parse error at or near "[" > >> > >> > > > >You need to double the quotes in the function body, > viz > > > > ... > > return ($1 ~ ''[0-9]''); > > ... > > > >Backslash-quote (\') is an alternative way. > > > > regards, tom lane > > > >---------------------------(end of > broadcast)--------------------------- > >TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > > > > > -- > Ron St.Pierre > Syscor R&D > tel: 250-361-1681 > email: rstpierre@syscor.com > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
I guess my note wasn't clear enough. When I said that worked, I meant
using the double quotes when trying to use regular expressions in a
function. I placed the working function, called numeric_part(text), on
the cookbook web page -> that's what I wanted to convey in the first two
sentences of my response.
eg:
planroomsk=# select numeric_part('Testing on my 7.2.1 install');
numeric_part
--------------
721
(1 row)
Jeff Eckermann wrote:
> --- "Ron St.Pierre" <rstpierre@syscor.com> wrote:
>
>>Thanks everyone, that worked and I now have a new
>>plpgsql function to
>>extract numbers from a string. I placed it in the
>>PL/pgSQL CookBook
>>(linked from the Users Lounge on the postgres home
>>page ->
>>http://www.brasileiro.net:8080/postgres/cookbook/)
>>if anyone wants it.
>
>
> You mean this _works_ for you??? I doesn't for me,
> and I don't see how it could. I see a return type
> mismatch, for starters: $1 ~ ''[0-9]'' will return
> boolean, whereas your function is defined to return an
> integer. Testing on my 7.2.1 installation gives this
> error:
> jeck=# select getnumber('1');
> NOTICE: Error occurred while executing PL/pgSQL
> function getnumber
> NOTICE: while casting return value to functions
> return type
> ERROR: pg_atoi: error in "t": can't parse "t"
>
>
>>Tom Lane wrote:
>>
>>
>>>"Ron St.Pierre" <rstpierre@syscor.com> writes:
>>>
>>>
>>>
>>>>CREATE OR REPLACE FUNCTION getnumber(varchar(1))
>>
>>RETURNS integer AS '
>>
>>>> BEGIN
>>>> return ($1 ~ '[0-9]');
>>>> END;
>>>>' LANGUAGE 'plpgsql';
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>>I get the following error:
>>>> parse error at or near "["
>>>>
>>>>
>>>
>>>You need to double the quotes in the function body,
>>
>>viz
>>
>>> ...
>>> return ($1 ~ ''[0-9]'');
>>> ...
>>>
>>>Backslash-quote (\') is an alternative way.
>>>
>>> regards, tom lane
>>>
>>>---------------------------(end of
>>
>>broadcast)---------------------------
>>
>>>TIP 1: subscribe and unsubscribe commands go to
>>
>>majordomo@postgresql.org
>>
>>>
>>>
>>
>>
>>--
>>Ron St.Pierre
>>Syscor R&D
>>tel: 250-361-1681
>>email: rstpierre@syscor.com
>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please
>>send an appropriate
>>subscribe-nomail command to majordomo@postgresql.org
>>so that your
>>message can get through to the mailing list cleanly
>
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
>
--
Ron St.Pierre
Syscor R&D
tel: 250-361-1681
email: rstpierre@syscor.com