Re: is_numeric() or extract_numeric() functions?

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