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

On Tue, Mar 24, 2026 at 4:20 AM Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:
>
> Surya and I did a pair-review of this. In addition to the notes above (which we agree with), we have the following notes:
>
> + if (inputBaseTypeId == targetBaseTypeId)
> + ereport(ERROR,
> + errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> + errmsg("cannot cast type %s to %s while using a format template",
> + format_type_be(inputBaseTypeId),
> + format_type_be(targetBaseTypeId)),
> + errdetail("binary coercible type cast is not supported while using a format template"),
> + parser_coercion_errposition(pstate, location, node));
>
> This could use a bit more explanation in a comment - is it because there is no plausible type that can take a FORMAT and be cast to itself?
>

We might be able to do it in the future; it's currently not allowed.
I have used the ERRCODE_FEATURE_NOT_SUPPORTED error code.

We should not use
``> + if (inputBaseTypeId == targetBaseTypeId)``
We need to use IsBinaryCoercible.

src1=# SELECT CAST('52'::int2 as numeric FORMAT '9 9 9 9 9 9 . 9 9');
ERROR: function pg_catalog.to_number(smallint, text) does not exist
DETAIL: No function of that name accepts the given argument types.
HINT: You might need to add explicit type casts.

The above CAST FORMAT error message is not ideal, therefore we need
stricter type restrictions for source and target data types,
and more type checking.
The target type must exactly match the function's result type.
to_char, to_date, to_number, to_timestamp.

> + if (s_typcategory != TYPCATEGORY_NUMERIC &&
> + s_typcategory != TYPCATEGORY_STRING &&
> + s_typcategory != TYPCATEGORY_DATETIME &&
> + s_typcategory != TYPCATEGORY_TIMESPAN)
>
> In situations like this, the committers have shown a strong preference for switch() statements. Though it may make more sense to package this if into a static function is_formattable_type() or similar.
>

we need more restriction on CAST FORMAT source type and target type.
pg_type->typcategory is not reliable for disallowing source/target types,
So, I use switch() to enumerate all allowed data types and the
switch() default branch handles ereport(ERROR).

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