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<[email protected]> 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
