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