CREATE FUNCTION isnumeric(text) RETURNS boolean AS '
SELECT $1 ~ ''^[0-9]+$''
' LANGUAGE 'sql';

SELECT '1E10' ~ '^[0-9]+$';
--
f

PREPARE foo(text) AS 
(SELECT $ 1 ::numeric);

EXECUTE foo('1.123E-10');
--
 0 . 0000000001123 
EXECUTE foo('s1.123E10');
--
ERROR:  invalid input syntax for type numeric: "s1.123E10"

********** Ошибка **********

ERROR: invalid input syntax for type numeric: "s1.123E10"
SQL state: 22P02

CREATE FUNCTION isnumeric_0(text) RETURNS boolean AS $$
SELECT $ 1  ~ '^[0-9]+$'
$$
 immutable LANGUAGE 'sql';
--
CREATE FUNCTION isnumeric_1(IN _input anyelement) RETURNS boolean AS $$
BEGIN 
	PERFORM _input::numeric;
	RETURN TRUE;
EXCEPTION
	WHEN OTHERS THEN
	RETURN FALSE;
END;
$$
 immutable LANGUAGE 'plpgsql';
--
CREATE TABLE test_t
(
  id serial,  
  textv text,
  numv numeric,
  CONSTRAINT test_t_pk primary key (id)
)
WITH (
  OIDS=FALSE
);
insert INTO  test_t(textv,numv) 
SELECT  CASE WHEN (random()>= 0 . 5 ) THEN 'v' ELSE '' END ||  s::text 
, s::numeric FROM generate_series( 1 , 10000 ) s
--
EXPLAIN ANALYZE
SELECT 
textv
,numv
FROM test_t
--
"Seq Scan on test_t  (cost=0.00..159.00 rows=10000 width=12) (actual time=0.020..11.764 rows=10000 loops=1)"
"Total runtime: 20.993 ms"
--
EXPLAIN ANALYZE
SELECT 
CASE WHEN isnumeric_0(textv) THEN textv::numeric ELSE NULL END AS numeric_
,textv
,CASE WHEN isnumeric_0(numv::text) THEN numv::numeric ELSE NULL END AS numv_
--ERROR:  function isnumeric_0(numeric) does not exist
,numv
FROM test_t
--
"Seq Scan on test_t  (cost=0.00..309.00 rows=10000 width=12) (actual time=0.041..35.616 rows=10000 loops=1)"
"Total runtime: 43.280 ms"
--не совсем бесплатно 
--
EXPLAIN ANALYZE
SELECT 
CASE WHEN isnumeric_1(textv) THEN textv::numeric ELSE NULL END AS numeric_
,textv
,CASE WHEN isnumeric_1(numv) THEN numv::numeric ELSE NULL END AS numv_
,numv
FROM test_t
--
"Seq Scan on test_t  (cost=0.00..5209.00 rows=10000 width=12) (actual time=0.275..381.080 rows=10000 loops=1)"
"Total runtime: 388.215 ms"
-- порядок