Thread: is_numeric() or extract_numeric() functions?

is_numeric() or extract_numeric() functions?

From

"Ron St.Pierre"

Date:

28 January 2003, 23:14:48

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

Re: is_numeric() or extract_numeric() functions?

From

"Rod Kreisler"

Date:

29 January 2003, 01:26:50

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
>
>

Re: is_numeric() or extract_numeric() functions?

From

Tom Lane

Date:

29 January 2003, 02:17:35

"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

Re: is_numeric() or extract_numeric() functions?

From

xach@xach.com (Zachary Beane)

Date:

29 January 2003, 02:52:46

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

Re: is_numeric() or extract_numeric() functions?

From

"Ron St.Pierre"

Date:

29 January 2003, 16:42:07

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

Re: is_numeric() or extract_numeric() functions?

From

Jeff Eckermann

Date:

30 January 2003, 14:17:19

--- "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

Re: is_numeric() or extract_numeric() functions?

From

"Ron St.Pierre"

Date:

30 January 2003, 15:34:18

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