The idea is simple -- introduce new "error-safe" calling mode of user
functions by passing special node through FunctCallInfo.context, in
which function should write error info and return instead of throwing
it. Also such functions should manually free resources before
returning an error. This gives ability to avoid PG_TRY/PG_CATCH and
subtransactions.
I tried something similar when trying to implement TRY_CAST (
https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver16) late last year. I also considered having a default datum rather than just returning NULL.
I had not considered a new node type. I had considered having every function have a "safe" version, which would be a big duplication of logic requiring a lot of regression tests and possibly fuzzing tests.
Instead, I extended every core input function to have an extra boolean parameter to indicate if failures were allowed, and then an extra Datum parameter for the default value. The Input function wouldn't need to check the value of the new parameters until it was already in a situation where it found invalid data, but the extra overhead still remained, and it meant that basically every third party type extension would need to be changed.
Then I considered whether the cast failure should be completely silent, or if the previous error message should instead be omitted as a LOG/INFO/WARN, and if we'd want that to be configurable, so then the boolean parameter became an integer enum:
* regular fail (0)
* use default silently (1)
* use default emit LOG/NOTICE/WARNING (2,3,4)
At the time, all of this seemed like too big of a change for a function that isn't even in the SQL Standard, but maybe SQL/JSON changes that.
If so, it would allow for a can-cast-to test that users would find very useful. Something like:
SELECT CASE WHEN 'abc' CAN BE integer THEN 'Integer' ELSE 'Nope' END
There's obviously no standard syntax to support that, but the data cleansing possibilities would be great.
