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

On Thu, Jul 9, 2026 at 3:18 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> On 04.07.26 03:09, jian he wrote:
> >> 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?
> >>
> >
> > https://www.postgresql.org/message-id/762ae707-7fdc-43d8-a77a-3a10d12ce21d%40postgresfriends.org
> >
> > After this implemented, we can begin fully supporting:
> >
> > <cast specification> ::=
> > CAST <left paren>
> > <cast operand> AS <cast target>
> > [ FORMAT <cast template> ]
> > [ <cast error behavior> ON CONVERSION ERROR ]
> > <right paren>
>
> The ON CONVERSION ERROR feature is independent of the FORMAT feature,
> isn't it?
>

Earlier in this thread, the concern was raised that this isn't "generic" enough:
CAST ... FORMAT is just letting the parser produce a FuncExpr, which makes the
whole patch more like a syntax sugar.

In response, people suggested making it more generic. So far we've tried: using
the data type's input/output functions, using the data type's
typformatin/typformatout functions, and introducing a new pg_formatter catalog.
But we haven't agreed on a concrete definition of what a sufficiently generic
and genuinely future-proof approach would look like.

What I'm saying is: even without that generic mechanism settled, having CAST ...
FORMAT producing a FuncExpr still has value on its own. See below.

The attached v12-0001 to v12-0003 is the same as in [1].
v12-0004 is added to demo that CAST(expr AS type FORMAT fmt) is useful
for more than just being syntactic sugar for the existing to_*
functions.
Note: v12-0004 is in a rough but functional state.
With v12-0004, we can now do:

src8=# select cast('2012-13-12' as date format 'YYYY-MM-DD' default
null on conversion error);
date
------

(1 row)

src8=# select cast('2012-13-12' as date format 'YYYY-MM-DD' );
ERROR: date/time field value out of range: "2012-13-12"

With this example, I hope I've convinced you that it's still worthwhile to have
the parser build a FuncExpr for CAST ... FORMAT, even though that same FuncExpr
could otherwise be produced by calling the to_* function directly.

[1]: https://www.postgresql.org/message-id/CACJufxGPp23XfP_V9zk7cSDJ6DCZEz4Jo3BUPtPfueNAe%3DC2Hg%40mail.gmail.com

--
jian
https://www.enterprisedb.com/