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

jian he <jian.universality@gmail.com>

From: jian he <jian.universality@gmail.com>
To: Vik Fearing <vik@postgresfriends.org>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, Corey Huinker <corey.huinker@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2025-08-12T06:37:10Z
Lists: pgsql-hackers

Attachments

hi.
please check the attached v4 patch.

1. For binary-coercible casts, if the format template is specified,
raise an error.
Example:
SELECT CAST('1'::text AS text FORMAT 'YYYY'::text); -- error
2. limited implementation — currently only supports to_char, to_date,
to_number, and to_timestamp.
3. coerce_to_target_type function is used in many places, refactoring
add another
argument seems not practical. So, I introduced a new function
coerce_to_target_type_fmt. Similarly, since coerce_type is difficult to
refactor too, I created a new function coerce_type_fmt.

At this stage, we have not modified any pg_cast entries.  Adding to_char,
to_date, etc., into pg_cast has implications that require more consideration
(see [1]).
Also for this patch, including these functions in pg_cast is not really
necessary to achieve the intended behavior.

[1] https://postgr.es/m/CACJufxF4OW=x2rCwa+ZmcgopDwGKDXha09qTfTpCj3QSTG6Y9Q@mail.gmail.com