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

On Wed, Jul 1, 2026 at 1:39 AM Haibo Yan <tristan(dot)yim(at)gmail(dot)com> wrote:
>
> Thanks for looking at the patches.
> I liked your suggestion to call these “format casts” rather than
> “formatters”, 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 should
take exactly two arguments, with the last one being text. If array types are 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 need 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-ad27bc2050b5%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/