MIME-Version: 1.0
References: 
 <CACJufxGqm7cYQ5C65Eoh1z-f+aMdhv9_7V=NoLH_p6uuyesi6A@mail.gmail.com>
 <4c358bf8-87e8-4760-83d5-a6f9fcd88907@postgresfriends.org>
 <CACJufxF4OW=x2rCwa+ZmcgopDwGKDXha09qTfTpCj3QSTG6Y9Q@mail.gmail.com>
 <1b702de4-86b0-4144-a2d0-38266296894f@postgresfriends.org>
 <CACJufxEH-8UPdbPoUoqNRaiOePw+s2W2DG4OpXtoSYDaW30oAg@mail.gmail.com>
 <CAKFQuwZXYxPw4wofq+zZ5D7nH0sHDBwr--qku9jWYv7dS+P8RQ@mail.gmail.com>
 <CACJufxGTeSnHn7w8r-qoQrqwCFQSC+CbiEthzpHKUd+bPr_0mA@mail.gmail.com>
 <bfcc5265-f527-4abb-9a20-75a063bc4f28@postgresfriends.org>
 <CACJufxGbsc3g6RZf0rZpKYxffK6vGkc_OsTmX54uS0kFcVBtvA@mail.gmail.com>
 <CAKFQuwaG00tsXc2oSQwjdSnPQ=JuH+T8Oeejb3sO5TeqUHsfUQ@mail.gmail.com>
 <CADkLM=cvhJBrCj3GV+jXfpb5+U1Z33nsvCXo-=ZkDyaSPbS-1g@mail.gmail.com>
 <CAKFQuwYrwM64YnvZ5aL1DEWG50uW-1SrXesSKkmvpxXXrxTW-g@mail.gmail.com>
 <82b4223e-84d4-432d-8167-dd698ce489f5@postgresfriends.org>
 <CACJufxH+soqLj_AuMQj-_jxunVQKX-HBQA_3_3vmV1jTRyZ1hA@mail.gmail.com>
 <CACJufxGhOOYnPtmsuVocXVNyx2gB3K68ze67CM4LUqQeW--S8g@mail.gmail.com>
 <CAN4CZFNnqWbyk3UNr3XAwtKJooH8LnbDUg-f2JD9z_rG=feT7w@mail.gmail.com>
 <CACJufxFVMYuVz63DteEjbMdvx2neoYcwSRb5vhO-03Tzx=B8Jw@mail.gmail.com>
 <CAN4CZFNeCfLzt5g90vf_Dw5BKW2u-QhVKo369XbO-vXYq4ci5g@mail.gmail.com>
 <CACJufxH4ELUjdGNi7vkHEA6L=G0SfE5abnFs5vjvEm3G-Jqsbw@mail.gmail.com>
 <CADkLM=dGxwVCp3y8=LU+8hQUPw61JELx2jh02i729YCex8UqWg@mail.gmail.com>
 <CA+TgmoaUzwAxa_qHSp9ybCkAhMjQRDuEAZW2BrSb1DTPiz4ZzA@mail.gmail.com>
 <CABXr29FyPC7terFF7E+r462BEHhYgv06oUVoBrhkH7xhshuE6A@mail.gmail.com>
 <CA+TgmoYW4_Ze_iT-o64VuFGqpm48hh=4dyZJwqiWfRPDTW+L+g@mail.gmail.com>
 <CABXr29HF+AHV0FNxQfHyN-ByW6-3+pTBe5Pxm23wRpBYOmfohA@mail.gmail.com>
 <CACJufxFM1cq+wj8ydmsPAYEh63g5rP2tdn+y3QOUrhaA1a+Sgg@mail.gmail.com>
 <CABXr29FJ94+DNwktmCjt=WNLmYzUAUREqr-P01CuiUksC8Y0SA@mail.gmail.com>
 <CAKFQuwbOgm=-Q-J1pRR30Xcg-RcCmSOJ5RFduoQgHNZ08OvxnQ@mail.gmail.com>
 <CABXr29Ed4NW4Uk=TBTBwWpJT1YbwYifHSQ7Ny_=o95YR=-c7RQ@mail.gmail.com>
 <CACJufxE=UyyPDyv1LhiKFUU7VGceH_9EwfiFXzcAeFY7R8gZeg@mail.gmail.com>
In-Reply-To: 
 <CACJufxE=UyyPDyv1LhiKFUU7VGceH_9EwfiFXzcAeFY7R8gZeg@mail.gmail.com>
From: Haibo Yan <tristan.yim@gmail.com>
Date: Wed, 1 Jul 2026 21:14:42 -0700
Message-ID: 
 <CABXr29HSONZT3s0CMLM77OKUOmDVqH8VnJwaf8=Pbu9MoE-kuQ@mail.gmail.com>
Subject: Re: implement CAST(expr AS type FORMAT 'template')
To: jian he <jian.universality@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>,
	Corey Huinker <corey.huinker@gmail.com>,
 Zsolt Parragi <zsolt.parragi@percona.com>,
	Vik Fearing <vik@postgresfriends.org>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CABXr29HSONZT3s0CMLM77OKUOmDVqH8VnJwaf8%3DPbu9MoE-kuQ%40mail.gmail.com>
Precedence: bulk

On Wed, Jul 1, 2026 at 8:23=E2=80=AFPM jian he <jian.universality@gmail.com=
> wrote:
>
> On Thu, Jul 2, 2026 at 8:01=E2=80=AFAM Haibo Yan <tristan.yim@gmail.com> =
wrote:
> >
> > CAST('{2025T01-01,2026T02-02}' AS date[] FORMAT 'YYYY"T"MM-DD')
> >
> > could reasonably mean: parse the array structure as usual, and apply th=
e
> > formatted text -> date conversion to each element.
> >
> > The current patch does not implement that.  It does an exact source/tar=
get
> > lookup in pg_format_cast, so an array target would currently require a =
format
> > cast for the array type itself.  But I agree that this may not be the b=
ehavior
> > users would expect, especially given the existing array coercion behavi=
or.
> >
> > If we decide that element-wise array formatting is required, one possib=
le
> > implementation would be to keep the scalar format-cast function signatu=
re as
> >
> > function(source_element_type, text) returns target_element_type
> >
> > and add a format-aware path in the array coercion logic.  The array exp=
ression
> > would iterate over elements and pass the same FORMAT expression to the =
scalar
> > format cast.  In that design, the array container itself would not need=
 a
> > separate format-cast entry.
> >
>
> In some cases, ArrayCoerceExpr might help, but not here.
>
> CAST('{2025T01-01,2026T02-02}'::TEXT AS date[] FORMAT 'YYYY"T"MM-DD')
>
> parse the array structure as usual, which array_in do all the heavy work.
> arrray_in(cstring, oid, integer).
> But once array_in is finished, we already get a date[] datum, then
> FORMAT is no longer needed.
> I also mentioned [1] that add a text argument to array_in is not possible=
.

Thanks, I agree that ArrayCoerceExpr is not enough for that case.
the normal array input path would already have to parse the elements as dat=
es
while constructing the date[] value, so the FORMAT clause cannot simply be
applied after array_in has returned.


>
> I guess not supporting arrays in the initial patch should be fine,
> since not all CASTs support CAST FORMAT.

I think that means array support needs a separate design.  For the initial
patch, it seems better not to support formatted casts involving array types=
 at
all.


> That raises another question: for user-defined CREATE FORMAT CAST,
> should we ban anyarray as the first argument type?

yes, I think polymorphic pseudo-types should becrejected.  The current patc=
h
already rejects pseudo-types, so anyarray should not be accepted.  But to
avoid future ambiguity, I think we should also reject concrete array source=
 or
target types for now.

Otherwise, a future element-wise array design would have to choose between =
an
exact array-level format cast and an element-wise scalar format cast.  I do=
n=E2=80=99t
think we want both meanings.  If array support is added later, my preferenc=
e
would be for it to mean element-wise application of a scalar format cast, w=
ith
the normal array machinery still handling the array container syntax.


> If we don't, will it conflict with array support once we implement it lat=
er?

So the initial rule could simply be: no polymorphic pseudo-types, and no ar=
ray
source or target types for `CREATE FORMAT CAST`.  If people agree, I can up=
date
the patch and documentation that way.

>
> [1]: https://www.postgresql.org/message-id/CACJufxGVuCM4XFGqaqiV-VOEiqMtC=
Z3%2BT-%2BSrG-y6kqdLo1ZqA%40mail.gmail.com

Regards,
Haibo