Обсуждение: Error handling inside PL/pgSQL functions

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

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