is_numeric() or extract_numeric() functions?

You forgot to escape the quotes:

CREATE OR REPLACE FUNCTION getnumber(varchar(1)) RETURNS integer AS '
BEGIN
return ($1 ~ ''[0-9]'');
END;
' LANGUAGE 'plpgsql';

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

"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

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