postgresql Postgres：定义 CAST 失败的默认值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10306830/
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
Postgres: define a default value for CAST failures?
提问by David Wolever
Is it possible to define a default value that will be returned in case a CASToperation fails?
是否可以定义在CAST操作失败时将返回的默认值？
For example, so that:
例如，这样：
SELECT CAST('foo' AS INTEGER)
Will return a default value instead of throwing an error?
会返回默认值而不是抛出错误吗？
回答by mu is too short
There is no default value for a CAST:
CAST没有默认值：
A type cast specifies a conversion from one data type to another. PostgreSQL accepts two equivalent syntaxes for type casts:
CAST ( expression AS type ) expression::type
类型转换指定从一种数据类型到另一种数据类型的转换。PostgreSQL 接受两种等效的类型转换语法：
CAST ( expression AS type ) expression::type
There is no room in the syntax for anything other than the expression to be casted and the desired target type.
除了要转换的表达式和所需的目标类型之外，语法中没有任何空间。
However, you can do it by hand with a simple function:
但是，您可以使用一个简单的函数手动完成：
create or replace function cast_to_int(text, integer) returns integer as $$
begin
return cast( as integer);
exception
when invalid_text_representation then
return ;
end;
$$ language plpgsql immutable;
Then you can say things like cast_to_int('pancakes', 0)and get 0.
然后你可以说像这样的东西cast_to_int('pancakes', 0)并得到0。
PostgreSQL also lets you create your own castsso you could do things like this:
PostgreSQL 还允许您创建自己的演员表，以便您可以执行以下操作：
create or replace function cast_to_int(text) returns integer as $$
begin
-- Note the double casting to avoid infinite recursion.
return cast(::varchar as integer);
exception
when invalid_text_representation then
return 0;
end;
$$ language plpgsql immutable;
create cast (text as integer) with function cast_to_int(text);
Then you could say
那你可以说
select cast('pancakes'::text as integer)
and get 0or you could say
得到0或者你可以说
select cast(some_text_column as integer) from t
and get 0for the some_text_columnvalues that aren't valid integers. If you wanted to cast varchars using this auto-defaulting cast then you'd have to double cast:
并获取不是有效整数0的some_text_column值。如果您想varchar使用此自动默认转换来转换s ，那么您必须进行双重转换：
select cast(some_varchar::text as integer) from t
Just because you can do this doesn't make it a good idea. I don't think replacing the standard text to integer cast is the best idea ever. The above approach also requires you to leave the standard varcharto integercast alone, you could get around that if you wanted to do the whole conversion yourself rather than lazily punting to the built in casting.
仅仅因为您可以做到这一点并不能使它成为一个好主意。我不认为将标准文本替换为整数转换是有史以来最好的主意。上述方法还要求您单独使用标准varchar进行integer转换，如果您想自己进行整个转换而不是懒惰地转向内置转换，则可以解决这个问题。
NULL handling is left as an (easy) exercise for the reader.
NULL 处理留给读者作为一个（简单的）练习。
回答by RThomas
Trap the error as described in documentation and then specify an action to do instead.
按照文档中的描述捕获错误，然后指定要执行的操作。
Documentation on error trapping for PostgreSQLSnippet included below.
下面包含有关 PostgreSQL代码段的错误捕获的文档。
35.7.5. Trapping Errors
35.7.5. 捕获错误
By default, any error occurring in a PL/pgSQL function aborts execution of the function, and indeed of the surrounding transaction as well. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION clause. The syntax is an extension of the normal syntax for a BEGIN block:
默认情况下，PL/pgSQL 函数中发生的任何错误都会中止该函数的执行，实际上也会中止周围事务的执行。您可以使用带有 EXCEPTION 子句的 BEGIN 块捕获错误并从中恢复。语法是 BEGIN 块的正常语法的扩展：
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;
If no error occurs, this form of block simply executes all the statements, and then control passes to the next statement after END. But if an error occurs within the statements, further processing of the statements is abandoned, and control passes to the EXCEPTION list. The list is searched for the first condition matching the error that occurred. If a match is found, the corresponding handler_statements are executed, and then control passes to the next statement after END. If no match is found, the error propagates out as though the EXCEPTION clause were not there at all: the error can be caught by an enclosing block with EXCEPTION, or if there is none it aborts processing of the function.
如果没有错误发生，这种形式的块简单地执行所有语句，然后控制传递到 END 之后的下一个语句。但是，如果语句中出现错误，则放弃对语句的进一步处理，并将控制传递到 EXCEPTION 列表。在列表中搜索与发生的错误匹配的第一个条件。如果找到匹配项，则执行相应的 handler_statements，然后控制传递到 END 之后的下一个语句。如果没有找到匹配项，错误就会传播出去，就好像 EXCEPTION 子句根本不存在一样：错误可以被带有 EXCEPTION 的封闭块捕获，或者如果没有则中止函数的处理。
