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

On Tue, Jul 22, 2025 at 8:26 PM Vik Fearing  wrote:
>
> The actual syntax is:
>
>
>  ::=
>      CAST 
>           AS 
>          [ FORMAT  ]
>          [  ON CONVERSION ERROR ]
>          
>
>
> "CONVERSION" is probably a noise word, but it is there because A) Oracle
> wanted it there, and B) it makes sense because if the  behavior> fails, that is still a failure of the entire CAST.
>
>
> The  is:
>
>  ::=
>      ERROR
>    | NULL
>    | DEFAULT 
>
>
> but I am planning on removing the NULL variant in favor of having the
>  be a .  So it
> would be either ERROR ON CONVERSION ERROR (postgres's current behavior),
> or DEFAULT NULL ON CONVERSION ERROR.
>
>
> 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.
>

hi.

>  ::=
>      ERROR
>    | NULL
>    | DEFAULT 

for 
I disallow it from returning a set, or using aggregate or window functions.
For example, the following three cases will fail:

+SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR); --error
+SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION ERROR); --error
+SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ERROR) --error
(ret_setint function is warped as (select 1 union all select 2))

for array coerce, which you already mentioned, i think the following
is what we expected.
+SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON
CONVERSION ERROR);
+  int4
+---------
+ {-1011}
+(1 row)

I didn't implement the [ FORMAT  ] part for now.
please check the attached regress test and tests expected result.