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

Solution 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;

Solution 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]*$

Comments

[AKhil N]

please check whether this is useful?

[AKhil N]

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