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

On Thu, Jul 2, 2026 at 10:06 PM Henson Choi <assam258(at)gmail(dot)com> wrote:
>>
>> Look like policy decisions
>> ==========================
>>
>> D. May the DEFAULT be an arbitrary expression, or only a literal/bind?
>>
>> We currently allow an arbitrary expression as the DEFAULT:
>>
>> SELECT CAST('x' AS int DEFAULT (length('abc') * 2) ON CONVERSION ERROR);
>> -- 6
>>
>> Oracle restricts the DEFAULT to a literal or bind variable:
>>
>> CAST(x AS NUMBER DEFAULT TO_NUMBER('bad') ON CONVERSION ERROR)
>> -- ORA-43907: Argument in DEFAULT ... ON CONVERSION ERROR clause
>> -- must be a literal or bind variable.
>>
>> which structurally prevents (C). This expression DEFAULT can itself
>> fail; if it is intended to stay, its evaluation scope needs to be
>> pinned down so the nested case in (C) cannot arise.
>>

Quote from [1]
<<>>
An example of B) above would be: CAST('five' AS INTEGER DEFAULT 'six' ON
CONVERSION ERROR). 'six' is no more an integer than 'five' is, so that
would error out because the conversion error does not happen on the
operand but on the default clause. CAST('five' AS INTEGER DEFAULT 6 ON
CONVERSION ERROR) would work.
<<>>

As you can see, evaluation of the DEFAULT defexpr can error out, the
standard doesn't distinguish between a parse stage failure and an execution
stage failure. I've added a sentence noting that evaluation of the default
expression may fail. If others feel more strongly that the default expression
should be reduced to a Const after eval_const_expressions, we can certainly
enforce that during eval_const_expressions.

-----------------------------------------------------------------
Please check the rebased v32. Minor changes made in v32:

Previously, the source expression could be an UNKNOWN Const, or an UNKNOWN Const
wrapped inside a nested CollateExpr node. I added logic to coerce both cases to
TEXTOID.

in v31:
CAST('2022-01-01' COLLATE "C" COLLATE "POSIX" AS DATE DEFAULT NULL ON
CONVERSION ERROR)
would deparse back to itself.
Now it deparses as:
CAST('2022-01-01'::text COLLATE "POSIX" AS DATE DEFAULT NULL ON
CONVERSION ERROR)

Renamed EXPR_KIND_CAST_DEFAULT to EXPR_KIND_TYPECAST_DEFAULT.

In gram.y, renamed makeTypeCastWithDefault to makeTypeCastOpt, since another
patch (https://commitfest.postgresql.org/patch/5957) will be adding another
field to the TypeCast struct, a more generic name like makeTypeCastOpt will be
more future-proof.

[1]: https://www.postgresql.org/message-id/762ae707-7fdc-43d8-a77a-3a10d12ce21d%40postgresfriends.org

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