Re: implement CAST(expr AS type FORMAT 'template') - Mailing list pgsql-hackers

Hi Jian,

Thanks for looking at this.

On Wed, Jul 1, 2026 at 6:45 AM jian he <jian.universality@gmail.com> wrote:
>
> On Wed, Jul 1, 2026 at 1:39 AM Haibo Yan <tristan.yim@gmail.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.

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