Error handling inside PL/pgSQL functions

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.

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

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

On Thu, 2006-10-12 at 16:12 -0400, 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?

Regards,

--
Germán Hüttemann

<http://www.postgresql.org/docs/8.1/static/plpgsql-control-
structures.html#PLPGSQL-ERROR-TRAPPING>

Does this help?

Regards,
Jeff Davis