On Mon, Jul 6, 2026 at 1:14 PM Haibo Yan <[email protected]> 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
