Re: is_numeric() or extract_numeric() functions?

--- "Ron St.Pierre" <rstpierre@syscor.com> wrote:
> Thanks everyone, that worked and I now have a new
> plpgsql function to
> extract numbers from a string. I placed it in the
> PL/pgSQL CookBook
> (linked from the Users Lounge on the postgres home
> page ->
> http://www.brasileiro.net:8080/postgres/cookbook/)
> if anyone wants it.

You mean this _works_ for you???  I doesn't for me,
and I don't see how it could.  I see a return type
mismatch, for starters: $1 ~ ''[0-9]'' will return
boolean, whereas your function is defined to return an
integer.  Testing on my 7.2.1 installation gives this
error:
jeck=# select getnumber('1');
NOTICE:  Error occurred while executing PL/pgSQL
function getnumber
NOTICE:  while casting return value to functions
return type
ERROR:  pg_atoi: error in "t": can't parse "t"

> Tom Lane wrote:
>
> >"Ron St.Pierre" <rstpierre@syscor.com> writes:
> >
> >
> >>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 "["
> >>
> >>
> >
> >You need to double the quotes in the function body,
> viz
> >
> >    ...
> >            return ($1 ~ ''[0-9]'');
> >    ...
> >
> >Backslash-quote (\') is an alternative way.
> >
> >            regards, tom lane
> >
> >---------------------------(end of
> broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
> >
> >
>
>
> --
> Ron St.Pierre
> Syscor R&D
> tel: 250-361-1681
> email: rstpierre@syscor.com
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com