Re: CAST(... ON DEFAULT) - WIP build on top of Error-Safe User Functions

On Thu, Nov 6, 2025 at 6:00 AM Corey Huinker <corey.huinker@gmail.com> wrote:
>
> Issue 5:
>
> I think 0019 is a bit big for a committer to digest all in one sitting. Currently it:
>
> - introduces the type-safe cast node
> - introduces the cast on default syntax
> - redefines
> - adds in test cases for all safe functions defined in patches 1-18.
>
> As tedious as it might be, I think we want to move this patch to the front, move all pg_cast.dat changes to their respective patches that introduce that datatype's safe typecast function, as well as the test cases that are made possible by that new safe typecast. That will make it easier to ensure that each new cast has test coverage.
>
> Yes, that's going to be a lot of catversion bumps, requiring somebody to fudge the dates as we presently only allow for 10 catversion bumps in a day, but the committer will either combine a few of the patches or spread the work out over a few days.
>

Currently, patches v9-0001 through v9-0018 focus solely on refactoring
pg_cast.castfunc.  This refactoring is already valuable on its own because it
establishes the infrastructure needed for error safe type casts.
As mentioned before, to make
CAST(source_expr AS target_type DEFAULT expr ON CONVERSION ERROR);
work,
we cannot just simply replace casting FuncExpr nodes with CoerceViaIO, since
type modifiers behave differently in these two Nodes.
(e.g., casting numeric 1.11 to integer is not equivalent to casting the literal
"1.11" to integer).

Also, are we settled on this new pg_cast column name (pg_cast.casterrorsafe)?
Overall, I think it's better not to touch pg_cast.dat in each of these
refactoring patches.

Without first refactoring pg_cast.castfunc (01 to 18), making CAST ...
DEFAULT as the first patch (0001)
won't work, since pg_cast.castfunc itself is not error safe yet, and we
have no way to test the CAST DEFAULT syntax.

So we have to *first* refactor pg_cast.castfunc, make it error safe
then implement CAST DEFAULT.
--------------------------------------------------
The CAST DEFAULT patch is large, I tried to split some newly created function
into a seperate patch.
see v10-0019-invent-some-error-safe-functions.patch.

SELECT CAST('five' AS INTEGER DEFAULT 6 ON CONVERSION ERROR);
is not hard to make it error safe. ('five' is a simple Cons node)

However, I found out, to make
SELECT CAST('five'::INTEGER AS INTEGER DEFAULT 6 ON CONVERSION ERROR);
error safe is hard.

('five'::INTEGER) is a TypeCast node, normally it will error out in
transformTypeCast->
coerce_to_target_type->coerce_type ```(if (inputTypeId == UNKNOWNOID
&& IsA(node, Const)))```
If we do not error out, then we need a Node to represent the failed cast, mainly
for deparse purposes.

that means for CAST(source_expr .... DEFAULT defexpr ON CONVERSION ERROR);
The only corner case we handle is when source_expr is a simple Const node.
In all other cases, source_expr is processed through transformExpr,
which does all
the normal parse analysis for a node.

--
jian
https://www.enterprisedb.com/