isnumeric - checking if text variable is convertable to numeric

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/

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 ===