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

On Tue, Jul 22, 2025 at 8:26 PM Vik Fearing <vik@postgresfriends.org> wrote:
>
> The actual syntax is:
>
>
> <cast specification> ::=
>      CAST <left paren>
>          <cast operand> AS <cast target>
>          [ FORMAT <cast template> ]
>          [ <cast error behavior> ON CONVERSION ERROR ]
>          <right paren>
>
>
> "CONVERSION" is probably a noise word, but it is there because A) Oracle
> wanted it there, and B) it makes sense because if the <cast error
> behavior> fails, that is still a failure of the entire CAST.
>
>
> The <cast error behavior> is:
>
> <cast error behavior> ::=
>      ERROR
>    | NULL
>    | DEFAULT <value expression>
>
>
> but I am planning on removing the NULL variant in favor of having the
> <value expression> be a <contextually typed value specification>.  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.

> <cast error behavior> ::=
>      ERROR
>    | NULL
>    | DEFAULT <value expression>

for <value expression>
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 <cast template> ] part for now.
please check the attached regress test and tests expected result.