Re: is_numeric() or extract_numeric() functions? - Mailing list pgsql-general

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