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

On Mon, Jul 6, 2026 at 11:18 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Mon, Jul 6, 2026 at 1:14 PM Haibo Yan <tristan(dot)yim(at)gmail(dot)com> wrote:
> > The cases where I think a generic mechanism could be useful are
> > broader than that. For example:
> >
> > text <-> date/time using datetime templates
> > text <-> numeric using number templates
> > text <-> bytea using hex/base64/escape
> > text/bytea <-> extension types
> > using external representations such as
> > WKT/WKB/GeoJSON for geometry types
> >
> > The first two are close to the existing formatting functions. The bytea
> > case is currently handled by encode()/decode(), but it has the same general
> > shape: a conversion between a typed value and a textual representation,
> > controlled by a format argument.
>
> Sure, but in each case, the conversion function in question could just
> be called directly.
>
> It's also noteworthy that in each of these examples, one of the two
> types is text, or maybe bytea, which, again, really makes you wonder
> why this is designed as a cast-like mechanism. I guess you could have
> CAST(now() to 'integer' FORMAT 'YYYY') but that seems fairly silly, so
> I really don't understand why this is designed to take two arbitrary
> types.
>
> Aside from standards-compliance, the only value I can see in a feature
> like this is if it's helpful to be able to name the type rather than
> naming the conversion function. For instance, imagine that I could use
> the same format string for conversions between a bunch of different
> types. Then potentially it's handy to be able to write CAST(something
> AS text FORMAT 'the string I always use') and you don't need to think
> about which source type you've got at this particular call site. But
> there are multiple problems with that idea. First, it seems unlikely
> that the format string would be generic in that way. And second, we
> already support function overloading, so you could just pick some
> function name (like to_char!) and overload it to do the same thing.
> Picking a function name also has the advantage of not privileging one
> particular way of doing a conversion between two types over all
> others, whereas this FORMAT CAST proposal requires you to decide on
> one canonical method of formatting type X as type Y.
>
> (Regular casts have this problem, too, to an extent.)
>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com

Hi Robert,

Thanks, I think that is a fair criticism.

The examples I listed are mostly conversions between a typed value and a
textual or binary representation, not arbitrary conversions between unrelated
types. So I agree that the current pg_format_cast design may be too broad if
it allows arbitrary source/target pairs.

I also see your point about function overloading. If the only goal is generic
dispatch, an overloaded function name already provides that. So the value of
CAST(... FORMAT ...) would need to be narrower than that: a standard syntax
for a type’s formatted external representation, not a general replacement for
named conversion functions.

Maybe the better boundary is that a formatted conversion should be between a
data type and an external representation type, probably text or bytea.

That would allow cases such as text -> date, numeric -> text, and
bytea -> some_type, and also the existing-style text <-> bytea encoding
case. But it should not become a generic text -> text or bytea -> bytea
transformation mechanism, nor a second cast system for arbitrary type pairs.

Regards,
Haibo