Re: is_numeric() or extract_numeric() functions?

--- "Ron St.Pierre" <rstpierre(at)syscor(dot)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(at)syscor(dot)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(at)postgresql(dot)org
> >
> >
>
>
> --
> Ron St.Pierre
> Syscor R&D
> tel: 250-361-1681
> email: rstpierre(at)syscor(dot)com
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)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