Обсуждение: isnumeric - checking if text variable is convertable to numeric

I am writing a pgsql function and I would need to create a condition
based on the value of a text variable. I would need to check if
text_var is convertable to numeric type and if yes do the conversion,
if no do something else. I couldn't find out how to do that.

Something like:
IF isnumeric(text_var) Then
 num_var:=Cast (text_var AS numeric);
ELSE
....
END IF;

Thanks for the help.
Balázs

On Mon, Apr 24, 2006 at 05:31:46AM -0700, SunWuKung wrote:
> I am writing a pgsql function and I would need to create a condition
> based on the value of a text variable. I would need to check if
> text_var is convertable to numeric type and if yes do the conversion,
> if no do something else. I couldn't find out how to do that.

I'd use pattern matching:

test=# select a, textregexeq(a,'^[[:digit:]]+(\.[[:digit:]]+)?$') as
isnumeric from test;
   a    | isnumeric
--------+-----------
 string | f
 100.   | f
 100    | t
 100.04 | t
 .04    | f
 0.04   | t
 0.04d  | f
(7 rows)

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

I was trying to actually do a cast and trap the error but (beside not
having succeeded in doing so) this is a much nicer way.

Thanks.
Balázs

On 24 Apr 2006 12:49:51 -0700, SunWuKung <Balazs.Klein@t-online.hu> wrote:
> I was trying to actually do a cast and trap the error but (beside not
> having succeeded in doing so) this is a much nicer way.
>
> Thanks.
> Balázs
>
>

in 8.1 you can use exception blocks

BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

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

--
regards,
Jaime Casanova

"What they (MySQL) lose in usability, they gain back in benchmarks, and that's
all that matters: getting the wrong answer really fast."
                           Randal L. Schwartz

I tried this but couldn't find out what would be the WHEN condition for

Select Cast('asdf' as numeric)
ERROR:  invalid input syntax for type numeric

regards,
Balázs

am  25.04.2006, um  2:01:49 -0700 mailte SunWuKung folgendes:
> I tried this but couldn't find out what would be the WHEN condition for
>
> Select Cast('asdf' as numeric)
> ERROR:  invalid input syntax for type numeric

create or replace function check_numeric(varchar) returns bool as $$
declare
        i numeric;
begin
        i := $1::numeric;
        return 't'::bool;
        EXCEPTION WHEN invalid_text_representation then
                return 'f'::bool;
end;
$$ language plpgsql immutable strict;



HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===