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

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