currently a failed cast throws an error. It would be useful to have a
way to get a default value instead.
I've recently encountered situations where this would have been helpful. Recently I came across some client code:
CREATE OR REPLACE FUNCTION is_valid_json(str text) RETURNS boolean LANGUAGE PLPGSQL
AS $$
DECLARE
j json;
BEGIN
j := str::json;
return true;
EXCEPTION WHEN OTHERS THEN return false;
END
$$;
This is a double-bummer. First, the function discards the value so we have to recompute it, and secondly, the exception block prevents the query from being parallelized.
T-SQL has try_cast [1]
I'd be more in favor of this if we learn that there's no work (current or proposed) in the SQL standard.
Oracle has CAST(... AS .. DEFAULT ... ON CONVERSION ERROR) [2]
If the SQL group has suggested anything, I'd bet it looks a lot like this.
The DEFAULT ... ON CONVERSION ERROR syntax seems like it could be
implemented in PostgreSQL. Even if only DEFAULT NULL was supported (at
first) that would already help.
The short syntax could be extended for the DEFAULT NULL case, too:
SELECT '...'::type -- throws error
SELECT '...':::type -- returns NULL
I think I'm against adding a ::: operator, because too many people are going to type (or omit) the third : by accident, and that would be a really subtle bug. The CAST/TRY_CAST syntax is wordy but it makes it very clear that you expected janky input and have specified a contingency plan.
The TypeCast node seems like it wouldn't need too much modification to allow for this. The big lift, from what I can tell, is either creating versions of every $foo_in() function to return NULL instead of raising an error, and then effectively wrapping that inside a coalesce() to process the default. Alternatively, we could add an extra boolean parameter ("nullOnFailure"? "suppressErrors"?) to the existing $foo_in() functions, a boolean to return null instead of raising an error, and the default would be handled in coerce_to_target_type(). Either of those would create a fair amount of work for extensions that add types, but I think the value would be worth it.
I do remember when I proposed the "void"/"black hole"/"meh" datatype (all values map to NULL) I ran into a fairly fundamental rule that types must map any not-null input to a not-null output, and this could potentially violate that, but I'm not sure.
Does anyone know if the SQL standard has anything to say on this subject?