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

Hi Jian,

Thanks for looking at this.

On Wed, Jul 1, 2026 at 6:45 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> 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?

yes, I agree that we should get agreement on the DDL syntax before going
much further.

I see the analogy with the earlier SAFE discussion, but I think this case is a
little different. This patch creates a separate object kind rather than adding
another option to ordinary CREATE CAST

>
> Does the following alternative syntax make sense for CAST FORMAT?

My reason for preferring CREATE FORMAT CAST is that a format cast is not
really an ordinary cast with one more attribute. Ordinary casts live in
pg_cast and are tied to things like cast context and cast method. A format
cast is only considered for

CAST(expr AS type FORMAT fmt)

and not for ordinary casts without a FORMAT clause, assignment casts, or
implicit casts. It also always needs a function whose second argument is the
FORMAT expression, not the destination typmod argument used by ordinary cast
functions.
So I think

CREATE CAST (...) WITH FUNCTION ... AS FORMAT

would make this look more like a pg_cast entry than it really is. My
preference is to keep format casts separate from ordinary casts, both in the
catalog and in the DDL syntax. Of course, if others prefer a different
spelling, we should settle that in the thread.

>
> 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).

I don’t feel this patch should imply automatic element-wise formatting.
The current design is an exact source/target lookup in pg_format_cast. So in
this case, after resolving the source expression, the lookup would be for a
format cast from the source type to date[] itself. It would not automatically
fall back to a text -> date format cast and apply it to each array element.

That does not rule out element-wise array support later. If we want that, one
possible design would be to mirror the existing array-coercion approach: look
up the scalar format cast for the element types and wrap it in an array-mapping
expression. In that design, the scalar format-cast function signature would
still remain

function(source_element_type, text) returns target_element_type

and the array expression node would be responsible for iterating over the
array.

>
> 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.

I think that should be a separate design decision. The important point for
this patch is that it does not define automatic element-wise formatting, and I
don’t think we need to change the basic scalar function signature now in order
to leave room for such a design.

If people agree with this interpretation, I can add a sentence to the
documentation saying that format cast lookup is by exact source and target
type, and that the FORMAT clause is not automatically applied to array
elements.

>
> [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/

Regards,
Haibo