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

On Thu, Jun 18, 2026 at 10:52 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Tue, Mar 31, 2026 at 1:48 PM Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:
> > Everything's passing, moving the tests out of citext, etc into cast.sql is good.
> >
> > I think the next step it to bring each TODO and FIXME into the thread, and explain the factors that prevent you from being certain about what to do in those situations.
>
> IMHO, this patch as currently written is basically dead on arrival.
> The v7 patch still works by constructing calls to
> pg_catalog.{to_date,to_number,to_timestamp,to_char} based on the input
> data type. But I think it's been clearly stated on this thread that we
> need some kind of more generic infrastructure. Vik said it best: "we
> need to find a way to make this generic so that custom types can
> define formatting rules for themselves." I completely agree. The
> extensible type system in PostgreSQL is one of the project's greatest
> triumphs, and I do not think anyone is going to be enthusiastic about
> committing a patch that purports to implement a flavor of casting but
> is completely unextensible by out-of-core types and doesn't even cover
> all the in-core types for which it might be interesting. Even if
> someone is, -1 from me.
>
> I suggest backing up to David G. Johnston's comment here: "How about
> changing the specification for create type. Right now input functions
> must declare either 1 or 3 arguments. Let’s also allow for 2 and
> 4-argument functions where the 2nd or 4th is where the format is
> passed. If a data type input function lacks one of those signatures
> it is a runtime error if a format clause is attached to its cast
> expression. For output, we go from having zero input arguments to
> zero or one, with the same resolution behavior." I'm not sure that
> David's proposal here is really the best thing, but it's the kind of
> thing that *could* be right, i.e. a generic infrastructure that can
> work for any choice of data type.
>

I initially tried adding a 4th text argument to existing type input functions
(like date_in, array_in) to support format-driven casting from cstring to a
type's internal representation. This approach turned out to be a dead end due to
two irreconcilable constraints.

If the 4th argument defaults to NULL: pg_proc.proisstrict must be set to
false to prevent the function from short-circuiting and returning NULL whenever
no format is supplied. But eval_const_expressions relies on type input functions
being strict to safely constant-fold T_CoerceViaIO nodes — marking them all
non-strict would silently defeat a large class of optimizations.

If the 4th argument defaults to a non-NULL value: there is no sensible
sentinel, any chosen text value would be indistinguishable from a real format
string intentionally passed by the caller.

The fourth argument must have a DEFAULT value,
otherwise, we will have a duplicated function name for the data type
input function, which seems not ideal.
------------------------------------------------------------------
At first I thought the 4th argument on the input function was really
necessary. Consider:
CAST('{2022-21-01}' AS DATE[] FORMAT 'YYYY-DD-MM');
For this to work, we need converting each array element to date using
the specified
format — array_in would need to receive the format string somehow.

But given the analysis above, that path is closed. So the only remaining option
for making array format casting work is to duplicate a large portion of
array_in's logic into a dedicated array input formatting function:
array_typformatin.
------------------------------------------------------------------
This thread doesn't mention how CAST FORMAT deals with array types.
I imagine the mechanism is applies the format template to each array element.

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

Apply the format template ('YYYY-DD-MM') to ``2022-01-01`` and
``2022-21-01`` during processing date_in.
------------------------------------------------------------------
Similar to CREATE TYPE (typmodin/typmodout), the attached patch
extends the pg_type catalog,
add typformatin and typformatout to pg_type.

These two fields are the function OIDs for CAST FORMAT.

typformatin: the function converts a text value to the type's
internal representation given a format template. It must have
signature (text, text) -> type.

typformatout allows values of the data type to be converted to text using a
format template. It must have the signature (type, text) -> text,
where the second
argument is the format template.

With this patch, CAST(expr AS type FORMAT 'template') works for any data type
that has a valid typformatin or typformatout support function registered.
However, the feature remains tightly scoped to text-based conversions: FORMAT is
only meaningful when the cast target or source is text data type, CAST FORMAT
between two non-text types such as int8 to int4 is not supported.

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