Re: Error handling inside PL/pgSQL functions

On Thu, 2006-10-12 at 15:22 -0500, Tony Caduto wrote:
> Germán Hüttemann Arza wrote:
> > I am writing triggers procedures in PL/pgSQL and I need to handle some
> > errors inside the procedures.
> > Specifically, I am doing a CAST(char AS integer) and I want to know
> > when the char isn't a digit. How can I get do that?
> >
> Just off the top of my head I would say you could use a regular
> expression to check if the char is numeric value.
>
> something like this maybe:
>
> CREATE or REPLACE FUNCTION public.isnumeric(
> text)
> RETURNS pg_catalog.bool AS
> $BODY$
> SELECT $1 ~ '^[0-9]*(.[0-9]+)?$'
> $BODY$
> LANGUAGE 'sql' VOLATILE;
>
> You might have to modify the regular expression a bit as I was using it
> to test for doubles not just integers.
>

If you're using it to test for a double you might want to consider
scientific notation:

=> select '100000000000000000000000000000000'::float8;
float8
--------
1e+32
(1 row)

NUMERIC and FLOAT4/8 can use scientific notation as input. NUMERIC
doesn't appear to use it as an output representation, but floats do.

Also you want to consider negative numbers.

I know you just pulled this from your application, so I'm sure it works
for you. I just wanted to point out that there are more valid input
types than are represented in your regex.

Regards,
Jeff Davis