Corey Huinker <corey.huinker@gmail.com> writes: > The proposed changes are as follows: > CAST(expr AS typename) > continues to behave as before. > CAST(expr AS typename ERROR ON ERROR) > has the identical behavior as the unadorned CAST() above. > CAST(expr AS typename NULL ON ERROR) > will use error-safe functions to do the cast of expr, and will return > NULL if the cast fails. > CAST(expr AS typename DEFAULT expr2 ON ERROR) > will use error-safe functions to do the cast of expr, and will return > expr2 if the cast fails.
While I approve of trying to get some functionality in this area, I'm not sure that extending CAST is a great idea, because I'm afraid that the SQL committee will do something that conflicts with it. If we know that they are about to standardize exactly this syntax, where is that information available? If we don't know that, I'd prefer to invent some kind of function or other instead of extending the grammar.
While the syntax may change, the underlying infrastructure would remain basically the same: we would need the ability to detect that a typecast had failed, and replace it with the default value, and handle that at parse time, or executor time, and handle array casts where the array has the default but the underlying elements can't.
It would be simple to move the grammar changes to their own patch if that removes a barrier for people.