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
