PostgreSQL 9.3: isnumeric() in a condition

Question

I need to check whether the given text is numeric or not from the function.

Creating function for isnumeric():

CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$
DECLARE x NUMERIC;
BEGIN
    x = $1::NUMERIC;
    RETURN TRUE;
EXCEPTION WHEN others THEN
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Function from which I am calling the isnumeric() function:

create or replace function tm(var text)
returns varchar as
$$
begin
    if (select isnumeric(var))=t::BOOLEAN then
        raise info 'Is numeric value';
    else
        raise info 'Not numeric';
    end if;
end;
$$
language plpgsql;

Calling functon:

select tm('1');

Getting an error:

Here is the error details:

ERROR:  column "t" does not exist
LINE 1: SELECT (select isnumeric(var))=t::BOOLEAN

Epigraphic

You should have mentioned that you took this function from this answer – Unconsidered

Answer 1

You don't need a select (and it's actually wrong, as the error indicates) - just call isnumeric directly. Also, by the way, your function is missing a return statement.

To sum it all up:

create or replace function tm(var text)
returns varchar as
$$
begin
    if (isnumeric(var)) then -- call isnumeric directly
        raise info 'Is numeric value';
    else
        raise info 'Not numeric';
    end if;
    return '0'; -- missing return value in the OP
end;
$$
language plpgsql;

Lepidote

Answer 2

this will help you to identify your field is numeric or not:

select * from Table where field_name ~ '^[0-9]*$'

for decimal values you can use^[0-9.]*$ instead ^[0-9]*$

Resumption

please check whether this is useful? – Resumption

please check now , with '^[0-9.]*$' – Resumption

Answer 3

select getDataType('2021'); == Number
select getDataType('2021-05-12 23:12:10'); == Date
select getDataType('2021-05-12'); == Date
select getDataType('2X'); == String

CREATE 
    OR REPLACE FUNCTION getDataType ( TEXT ) RETURNS TEXT AS $$ DECLARE
    x VARCHAR;
BEGIN
    x = $1 :: NUMERIC;
RETURN 'Number';
EXCEPTION 
WHEN OTHERS THEN
BEGIN
    x = $1 :: DATE;
RETURN 'Date';
EXCEPTION 
WHEN OTHERS THEN
    RETURN 'String';

END;

END; 
$$ STRICT LANGUAGE plpgsql IMMUTABLE;

Thrasonical