Re: implement CAST(expr AS type FORMAT 'template')

On 27/07/2025 17:43, jian he wrote:
> hi.
>
> while working on CAST(... DEFAULT ON ERROR), I came across link[1].  I don't
> have access to the SQL standard, but based on the information in link[1], for
> CAST(val AS type FORMAT 'template'), I make the <cast template> as an A_Const
> node in gram.y.


Why does it have to be an A_const?  Shouldn't any a_expr work there?


> so the attached patch is to implement
>       CAST <left paren>
>           <cast operand> AS <cast target>
>           [ FORMAT <cast template> ]
>           <right paren>


This is correct syntax.  Thanks for working on it!


> The implementation is pretty straightforward.
> CAST(val AS type FORMAT 'template')
> internally, it will be transformed into a FuncExpr node whose funcid
> corresponds to
> function name as one of (to_number, to_date, to_timestamp, to_char).
> template as a Const node will make life easier.


This doesn't seem very postgres-y to me.  Wouldn't it be better to add 
something like castformatfuncid to pg_cast?  That way any types that 
have that would just call that.  It would allow extensions to add 
formatted casting to their types, for example.


> select proname, prosrc, proallargtypes, proargtypes,
> prorettype::regtype, proargnames
> from pg_proc
> where proname in ('to_number', 'to_date',  'to_timestamp', 'to_char');
>
> based on the query results, only a limited set of type casts are supported with
> formatted casts.  so error out early if the source or target type doesn't meet
> these conditions.  for example, if the source or target is a composite, array,
> or polymorphic type.


The standard is strict on what types can be cast to another, but I see 
no reason not to be more generic.

-- 

Vik Fearing