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

On Wed, Jul 1, 2026 at 8:23 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> On Thu, Jul 2, 2026 at 8:01 AM Haibo Yan <tristan(dot)yim(at)gmail(dot)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 the
> > formatted text -> date conversion to each element.
> >
> > The current patch does not implement that. It does an exact source/target
> > 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 behavior
> > users would expect, especially given the existing array coercion behavior.
> >
> > If we decide that element-wise array formatting is required, one possible
> > implementation would be to keep the scalar format-cast function signature as
> >
> > function(source_element_type, text) returns target_element_type
> >
> > and add a format-aware path in the array coercion logic. The array expression
> > 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 dates
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 patch
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 don’t
think we want both meanings. If array support is added later, my preference
would be for it to mean element-wise application of a scalar format cast, with
the normal array machinery still handling the array container syntax.

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

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

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

Regards,
Haibo