[postgis-users] isnumeric function

[Alexandre Dube]

Hi Paul,

  Sorry for being a bit clueless.  I'm trying to create the function 
while in command line but it's not working.  I tried while connected 
(using psql and the SQL statement only) and it worked, but I need to do 
it in command line (inside a script).

  Here's what I tried and didn't work :

psql -d mydb -c "CREATE FUNCTION isnumeric(text) RETURNS boolean AS 
'SELECT $1 ~ ''^[0-9]+$'' ' LANGUAGE 'sql'"

  === error message ===
ERROR:  operator is not unique: ~ unknown
LINE 1: ...CTION isnumeric(text) RETURNS boolean AS 'SELECT  ~ ''^[0-9]...
                                                             ^
HINT:  Could not choose a best candidate operator. You might need to add 
explicit type casts.

Any more clue ?

Alexandre

Paul Ramsey wrote:
> As your link suggests, Alexandre, there is no such function, but it
> can be done w/ a regex pattern, which postgresql supports.
>
> pramsey=# CREATE FUNCTION isnumeric(text) RETURNS boolean AS 'SELECT
> $1 ~ ''^[0-9]+$'' ' LANGUAGE 'sql';
>
> pramsey=# select isnumeric('this');
>  isnumeric
> -----------
>  f
> (1 row)
>
> pramsey=# select isnumeric('34');
>  isnumeric
> -----------
>  t
> (1 row)
>   


-- 
Alexandre Dubé
Mapgears
www.mapgears.com