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

On Thu, Jul 2, 2026 at 8:14 AM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> On 18.06.26 16:51, Robert Haas wrote:
> > The reason I somewhat hesitate to endorse that specific proposal is
> > that I'm not convinced that we should actually treat this as a form of
> > casting. Casts can be set to IMPLICIT or ASSIGNMENT or EXPLICIT, and
> > they can be WITHOUT FUNCTION or WITH INOUT, and none of that can be
> > relevant here. A CAST with FORMAT always needs to be implemented by a
> > function, is always explicit from a syntax point of view, and the code
> > to implement probably looks pretty different from the code needed for
> > a non-FORMAT cast. I am somewhat inclined to think we want something
> > that's like a cast function but actually a wholly separate mechanism,
> > e.g. a new pg_formatter catalog. I note that Jian He proposed putting
> > something in pg_type but I don't see how that can work, since there
> > are two types involved.
> >
> > I don't accept the argument that we should start with this and extend
> > it later. The patch as proposed is just syntactic sugar. Said another
> > way, there is existing syntax that already delivers the functionality.
> > So I don't see why we would rush out support for a bit of new syntax;
> > anyone who wants to use this functionality can already do so. Getting
> > the infrastructure right is, IMHO, the interesting part of the
> > project, and I think that work needs to be done first.
>
> I don't really understand the purpose of this feature to begin with.
> You can already call the formatting functions directly. Does this cast
> syntax offer anything on top of that?
>

I agree that the feature would be hard to justify if it only covered
to_date() and to_char() under another spelling.

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.

For extension types, the case may be stronger. PostGIS, for example, has
several external representations for geometry values: WKT, WKB, EWKT, GeoJSON,
and so on. A catalog-driven format-cast mechanism would let an extension
register those conversions without the parser knowing any of the function
names.

Other types might also have useful representation variants, such as UUIDs or
network/address types, but the point is that this is not limited to the current
date/time functions.

So I agree that this should not be sold as just syntax sugar for the existing
date/time functions. If it is worth doing, the value is the generic
infrastructure for FORMAT-aware conversions, including extension types, and not
just the initial built-in datetime cases.

Regards,
Haibo