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

On Thu, Jul 9, 2026 at 12:20 AM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> On 06.07.26 19:14, Haibo Yan 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
> One problem I see is that this would dictate that there is only one
> possible format language for each of these conversions.

There are other possible PostgreSQL-extension use cases beyond the standard
datetime/string case, for example bytea encodings, geometry external
representations, UUID display variants, and so on. But your point is that
these do not necessarily share a single natural FORMAT language for each
source/target pair.

For some of those cases, named functions are probably clearer, especially when
the alternatives are different representation families rather than different
templates within one family.

That seems to argue against making the first version a generic format-cast
facility for arbitrary type pairs. The safer scope is probably the standard
T839 case first, where the FORMAT clause has a well-defined datetime template
meaning. We can leave broader PostgreSQL-extension use cases for a later
discussion, if someone can show a model that does not force unrelated
representations through one canonical format function.

Regards,
Haibo