Message-ID: <4c358bf8-87e8-4760-83d5-a6f9fcd88907@postgresfriends.org>
Date: Sun, 27 Jul 2025 20:31:24 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: implement CAST(expr AS type FORMAT 'template')
To: jian he <jian.universality@gmail.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: 
 <CACJufxGqm7cYQ5C65Eoh1z-f+aMdhv9_7V=NoLH_p6uuyesi6A@mail.gmail.com>
Content-Language: en-US
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: 
 <CACJufxGqm7cYQ5C65Eoh1z-f+aMdhv9_7V=NoLH_p6uuyesi6A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/4c358bf8-87e8-4760-83d5-a6f9fcd88907%40postgresfriends.org>
Precedence: bulk


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