Horton

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

Vik Fearing <vik@postgresfriends.org>

View thread
From: Vik Fearing <vik@postgresfriends.org>
To: jian he <jian.universality@gmail.com>
Cc: Corey Huinker <corey.huinker@gmail.com>, Isaac Morland <isaac.morland@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2025-07-22T12:26:25Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Make cast functions to type money error safe

  2. Make cast function from circle to polygon error safe

  3. Make geometry cast functions error safe

  4. Make cast functions from jsonb error safe

  5. Make many cast functions error safe

  6. Add SQL/JSON query functions

  7. Add soft error handling to some expression nodes

On 22/07/2025 12:19, jian he wrote:
> On Tue, Jul 22, 2025 at 2:45 PM Vik Fearing <vik@postgresfriends.org> wrote:
>> It was accepted into the standard after 2023 was released.  I am the
>> author of this change in the standard, so feel free to ask me anything
>> you're unsure about.
>>
> is the generally syntax as mentioned in this thread:
> CAST(source_expression AS target_type DEFAULT default_expression ON ERROR)
>
> if so, what's the restriction of default_expression?


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.

-- 

Vik Fearing