MIME-Version: 1.0
References: 
 <CACJufxGqm7cYQ5C65Eoh1z-f+aMdhv9_7V=NoLH_p6uuyesi6A@mail.gmail.com>
 <4c358bf8-87e8-4760-83d5-a6f9fcd88907@postgresfriends.org>
 <CACJufxF4OW=x2rCwa+ZmcgopDwGKDXha09qTfTpCj3QSTG6Y9Q@mail.gmail.com>
 <1b702de4-86b0-4144-a2d0-38266296894f@postgresfriends.org>
 <CACJufxEH-8UPdbPoUoqNRaiOePw+s2W2DG4OpXtoSYDaW30oAg@mail.gmail.com>
 <CAKFQuwZXYxPw4wofq+zZ5D7nH0sHDBwr--qku9jWYv7dS+P8RQ@mail.gmail.com>
 <CACJufxGTeSnHn7w8r-qoQrqwCFQSC+CbiEthzpHKUd+bPr_0mA@mail.gmail.com>
 <bfcc5265-f527-4abb-9a20-75a063bc4f28@postgresfriends.org>
 <CACJufxGbsc3g6RZf0rZpKYxffK6vGkc_OsTmX54uS0kFcVBtvA@mail.gmail.com>
 <CAKFQuwaG00tsXc2oSQwjdSnPQ=JuH+T8Oeejb3sO5TeqUHsfUQ@mail.gmail.com>
 <CADkLM=cvhJBrCj3GV+jXfpb5+U1Z33nsvCXo-=ZkDyaSPbS-1g@mail.gmail.com>
 <CAKFQuwYrwM64YnvZ5aL1DEWG50uW-1SrXesSKkmvpxXXrxTW-g@mail.gmail.com>
 <82b4223e-84d4-432d-8167-dd698ce489f5@postgresfriends.org>
 <CACJufxH+soqLj_AuMQj-_jxunVQKX-HBQA_3_3vmV1jTRyZ1hA@mail.gmail.com>
 <CACJufxGhOOYnPtmsuVocXVNyx2gB3K68ze67CM4LUqQeW--S8g@mail.gmail.com>
 <tencent_73665D4D79A6E8901E43082F@qq.com>
In-Reply-To: <tencent_73665D4D79A6E8901E43082F@qq.com>
From: Corey Huinker <corey.huinker@gmail.com>
Date: Mon, 23 Mar 2026 16:20:14 -0400
Message-ID: 
 <CADkLM=fm35Nuf7++P815o+qHpa3WhHg=Fw=WzRS7NCrkRF2p5g@mail.gmail.com>
Subject: Re: implement CAST(expr AS type FORMAT 'template')
To: zengman <zengman@halodbtech.com>, suryapoondla4@gmail.com
Cc: jian he <jian.universality@gmail.com>,
	"David G. Johnston" <david.g.johnston@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>,
	Vik Fearing <vik@postgresfriends.org>
Content-Type: multipart/alternative; boundary="000000000000ed4b2b064db6c3a7"
Archived-At: 
 <https://www.postgresql.org/message-id/CADkLM%3Dfm35Nuf7%2B%2BP815o%2BqHpa3WhHg%3DFw%3DWzRS7NCrkRF2p5g%40mail.gmail.com>
Precedence: bulk

--000000000000ed4b2b064db6c3a7
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

>
>
> Typos
> - parse_expr.c:2745: "formmatted" =E2=86=92 "formatted"
> - parse_coerce.c:156: "Cocerce" =E2=86=92 "Coerce"
>
> In `coerce_type_with_format()` (parse_coerce.c):
>
> 1. The check `if (fmtcategory !=3D TYPCATEGORY_STRING && fmtcategory !=3D
> TYPCATEGORY_UNKNOWN)`
> could be moved earlier. It doesn't depend on any other
> calculations, so failing fast here would avoid unnecessary work.
>
> 2. consider using` list_make2(node, format)` instead of `list_make1() +
> lappend()`.
>
> --
> Regards,
> Man Zeng


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 =3D=3D 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?

+       if (s_typcategory !=3D TYPCATEGORY_NUMERIC &&
+               s_typcategory !=3D TYPCATEGORY_STRING &&
+               s_typcategory !=3D TYPCATEGORY_DATETIME &&
+               s_typcategory !=3D 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.

+       if (t_typcategory =3D=3D TYPCATEGORY_STRING)
+               funcname =3D list_make2(makeString("pg_catalog"),
+
makeString("to_char"));
...
+       else
+               elog(ERROR, "failed to find conversion function from %s to
%s while using a format template",
+                        format_type_be(inputTypeId),
format_type_be(targetTypeId));

Similar thing here, committers will probably want to see a switch.

+create function imm_const() returns text as $$ begin return 'YYYY-MM-DD';
end; $$ language plpgsql immutable;;
+select cast(col1 as date format imm_const()) from tcast;

double-;

Overall, the test coverage appears complete and the close mirroring of
existing tests makes the intention very clear.

--000000000000ed4b2b064db6c3a7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex"><br>
Typos<br>
- parse_expr.c:2745: &quot;formmatted&quot; =E2=86=92 &quot;formatted&quot;=
<br>
- parse_coerce.c:156: &quot;Cocerce&quot; =E2=86=92 &quot;Coerce&quot;<br>
<br>
In `coerce_type_with_format()` (parse_coerce.c):<br>
<br>
1. The check `if (fmtcategory !=3D TYPCATEGORY_STRING &amp;&amp; fmtcategor=
y !=3D TYPCATEGORY_UNKNOWN)`<br>
could be moved earlier. It doesn&#39;t depend on any other<br>
calculations, so failing fast here would avoid unnecessary work.<br>
<br>
2. consider using` list_make2(node, format)` instead of `list_make1() + lap=
pend()`.<br>
<br>
--<br>
Regards,<br>
Man Zeng</blockquote><div><br></div><div>Surya and I did a pair-review of t=
his. In addition to the notes above (which we agree with), we have the foll=
owing notes:<br><br>+ =C2=A0 =C2=A0 =C2=A0 if (inputBaseTypeId =3D=3D targe=
tBaseTypeId)<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ereport(=
ERROR,<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 errcode(ERRCODE_FEATURE_NOT_SUPPO=
RTED),<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 errmsg(&quot;cannot cast type %s =
to %s while using a format template&quot;,<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0format_type_be(inputBaseTypeId=
),<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0format_type_be(targetBaseTypeId)),<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 errdetail(&quot;binary coercible type cast is not supported while using=
 a format template&quot;),<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 parser_coerc=
ion_errposition(pstate, location, node));<br><br>This could use a bit more =
explanation in a comment - is it because there is no plausible type that ca=
n take a FORMAT and be cast to itself?</div><div><br></div><div>+ =C2=A0 =
=C2=A0 =C2=A0 if (s_typcategory !=3D TYPCATEGORY_NUMERIC &amp;&amp;<br>+ =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 s_typcategory !=3D TYPCATE=
GORY_STRING &amp;&amp;<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 s_typcategory !=3D TYPCATEGORY_DATETIME &amp;&amp;<br>+ =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 s_typcategory !=3D TYPCATEGORY_TIMESPAN)=
<br><br>In situations like this, the committers have shown a strong prefere=
nce for switch() statements. Though it may make more sense to package this =
if into a static function is_formattable_type() or similar.</div><div><br><=
/div><div>+ =C2=A0 =C2=A0 =C2=A0 if (t_typcategory =3D=3D TYPCATEGORY_STRIN=
G)<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 funcname =3D list_=
make2(makeString(&quot;pg_catalog&quot;),<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 makeString(&quot;to_char&quot;));<br>...<br>+ =C2=A0 =
=C2=A0 =C2=A0 else<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 el=
og(ERROR, &quot;failed to find conversion function from %s to %s while usin=
g a format template&quot;,<br>+ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0format_type_be(inputTypeId), forma=
t_type_be(targetTypeId));<br><br>Similar thing here, committers will probab=
ly want to see a switch.</div><div><br></div><div>+create function imm_cons=
t() returns text as $$ begin return &#39;YYYY-MM-DD&#39;; end; $$ language =
plpgsql immutable;;<br>+select cast(col1 as date format imm_const()) from t=
cast;<br><br>double-;<br><br>Overall, the test coverage appears complete an=
d the close mirroring of existing tests makes the intention very clear.</di=
v></div></div>

--000000000000ed4b2b064db6c3a7--