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>
In-Reply-To: 
 <CABXr29HF+AHV0FNxQfHyN-ByW6-3+pTBe5Pxm23wRpBYOmfohA@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Wed, 1 Jul 2026 21:44:37 +0800
Message-ID: 
 <CACJufxFM1cq+wj8ydmsPAYEh63g5rP2tdn+y3QOUrhaA1a+Sgg@mail.gmail.com>
Subject: Re: implement CAST(expr AS type FORMAT 'template')
To: Haibo Yan <tristan.yim@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>,
 Corey Huinker <corey.huinker@gmail.com>,
	Zsolt Parragi <zsolt.parragi@percona.com>,
 Vik Fearing <vik@postgresfriends.org>,
	"David G. Johnston" <david.g.johnston@gmail.com>,
	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/CACJufxFM1cq%2Bwj8ydmsPAYEh63g5rP2tdn%2By3QOUrhaA1a%2BSgg%40mail.gmail.com>
Precedence: bulk

On Wed, Jul 1, 2026 at 1:39=E2=80=AFAM Haibo Yan <tristan.yim@gmail.com> wr=
ote:
>
> Thanks for looking at the patches.
> I liked your suggestion to call these =E2=80=9Cformat casts=E2=80=9D rath=
er than
> =E2=80=9Cformatters=E2=80=9D, so I changed the series in that direction. =
 The DDL is
> now:
> ------------------------------------------------------
> CREATE FORMAT CAST (...)
> DROP FORMAT CAST (...)
> ------------------------------------------------------
> rather than CREATE/DROP FORMATTER, so the patch no longer adds
> FORMATTER as a keyword.
> I also renamed the related catalog and node names, so this now uses
> pg_format_cast and CoerceViaFormatCast.  The
> pg_dump/object-address/comment/extension code and the regression tests
> have been updated to use the new terminology as well.
> The rest of the design is the same as before: format casts are still
> catalog-driven, and both built-in and user-defined cases go through
> the same lookup path.
>

In an earlier thread [1], [2], I proposed introducing:

CREATE CAST (source_type AS target_type)
    WITH [SAFE] FUNCTION function_name [ (argument_type [, ...]) ]

That drew some pushback over non-standard conformance.
Here, we are introducing

CREATE FORMAT CAST (source_type AS target_type)
    WITH FUNCTION function_name [ (argument_type [, ...]) ]

This syntax is quite close to CREATE CAST.
per https://www.postgresql.org/docs/devel/sql-createcast.html
CREATE FORMAT CAST would be an extension to the standard, though we
already have non-standard precedent: AS IMPLICIT.

Before proceeding, do we need to reach consensus on the syntax itself?

Does the following alternative syntax make sense for CAST FORMAT?

CREATE CAST (source_type AS target_type)
    WITH FUNCTION function_name [ (argument_type [, ...]) ]
    AS FORMAT
---------------------------------------------------------------------------=
-------------
As I mentioned previously [3], there's an open question around this case:

CAST('{2022-01-01, 2022-21-01}' AS DATE[] FORMAT 'YYYY-DD-MM');

does this mean the format template ('YYYY-DD-MM') should be applied to each
element (2022-01-01, 2022-21-01) individually via the cast format function?

This matters because it affects check_format_cast_function's handling of
argument_type. According to create_format_cast.sgml, the format function sh=
ould
take exactly two arguments, with the last one being text. If array types ar=
e to
be supported, then argument_type would need to become either (source_type,
format_text) or (source_type, oid, int, format_text).

Sure, we can support formatted casts for array types later, but we still ne=
ed to
think through the array-type formatting design now, so that the cast format
function's argument types won't need to change later to accommodate arrays.

[1]: https://www.postgresql.org/message-id/5ae9578e-f25e-49c5-97ab-ad27bc20=
50b5%40eisentraut.org
[2]: https://www.postgresql.org/message-id/attachment/192833/v23-0023-error=
-safe-for-user-defined-CREATE-CAST.patch
[3]: https://www.postgresql.org/message-id/CACJufxGVuCM4XFGqaqiV-VOEiqMtCZ3=
%2BT-%2BSrG-y6kqdLo1ZqA%40mail.gmail.com


--
jian
https://www.enterprisedb.com/