PostgreSQL 9.3：条件中的 isnumeric()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28164186/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL 9.3: isnumeric() in a condition
提问by MAK
I need to check whether the given text is numeric or not from the function.
我需要从函数中检查给定的文本是否为数字。
Creating function for isnumeric():
创建函数isnumeric()：
CREATE OR REPLACE FUNCTION isnumeric(text) RETURNS BOOLEAN AS $$
DECLARE x NUMERIC;
BEGIN
x = ::NUMERIC;
RETURN TRUE;
EXCEPTION WHEN others THEN
RETURN FALSE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
Function from which I am calling the isnumeric()function:
我从中调用isnumeric()函数的函数：
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
回答by Mureinik
You don't need a select(and it's actually wrong, as the error indicates) - just call isnumericdirectly.
Also, by the way, your function is missing a returnstatement.
您不需要 a select（并且实际上是错误的，正如错误所示） - 只需isnumeric直接调用即可。另外，顺便说一下，您的函数缺少一个return语句。
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;
回答by AKhil N
this will help you to identify your field is numeric or not:
这将帮助您确定您的字段是否为数字：
select * from Table where field_name ~ '^[0-9]*$'
select * from Table where field_name ~ '^[0-9]*$'
for decimal values you can use^[0-9.]*$instead ^[0-9]*$
对于十进制值，您可以^[0-9.]*$改用^[0-9]*$
