[postgis-users] isnumeric function

[Paul Ramsey]

See how the $1 is missing in your error message? The shell is eating
that. Try escaping the $ characters with a \ in your shell call.

On Wed, Jun 10, 2009 at 12:29 PM, Alexandre Dube<adube at mapgears.com> wrote:
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>