From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David E. Wheeler" <david@justatheory.com>
cc: Andrew Dunstan <andrew@dunslane.net>,
        Peter Eisentraut <peter@eisentraut.org>,
        jian he <jian.universality@gmail.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Document DateStyle effect on jsonpath string()
In-reply-to: <3832087.1726070787@sss.pgh.pa.us>
References: <56955B33-6959-4FDA-A459-F00363ECDFEE@justatheory.com> <CACJufxHREyTFzKOWz3f-yD14HxAdfeP1vGQQ2SQrusZ+ziRJyA@mail.gmail.com> <4D874C72-8939-4083-8336-AB114D9E29AD@justatheory.com> <CACJufxHE6q47-GEf3Kk0pD+Snr3yZMM4Byig2=bQKV8js7sP-A@mail.gmail.com> <B8E7081B-A8BE-4BAF-9C6B-AACD4F6EAB00@justatheory.com> <F492B399-37CB-471C-AEAD-25E1C19B607D@justatheory.com> <A556B643-F7E6-442D-9235-2D46A8A887C1@justatheory.com> <3541398.1725994308@sss.pgh.pa.us> <D6137AEA-0BE7-4328-9718-71B7E6BB4F6C@justatheory.com> <b847bc16-57b4-4701-870b-c4ed33d71e51@eisentraut.org> <3654686.1725999372@sss.pgh.pa.us> <5e8879d0-a3c8-4be2-950f-d83aa2af953a@eisentraut.org> <3811774.1726063865@sss.pgh.pa.us> <E443FB23-3BEE-448D-95E4-F10904A10E5D@justatheory.com> <3824427.1726067484@sss.pgh.pa.us> <5801E26B-E2AB-4BA3-8B69-EF8837389284@justatheory.com> <3832087.1726070787@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Wed, 11 Sep 2024 12:06:27 -0400"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <3835809.1726071969.0@sss.pgh.pa.us>
Date: Wed, 11 Sep 2024 12:26:42 -0400
Message-ID: <3835867.1726072002@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3835867.1726072002%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3835809.1726071969.1@sss.pgh.pa.us>

I wrote:
> I think I'd be content to have string() duplicate that behavior
> --- in fact, it seems like it'd be odd if it doesn't match.

Building on that thought, maybe we could fix it as attached?
This changes the just-committed test cases of course, and I did
not look at whether there are documentation changes to make.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="fix-jsonpath-string-output-wip.patch";
	charset="us-ascii"
Content-ID: <3835809.1726071969.2@sss.pgh.pa.us>
Content-Description: fix-jsonpath-string-output-wip.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt=
/jsonpath_exec.c
index e3ee0093d4..b9c2443b65 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -72,6 +72,7 @@
 #include "utils/datetime.h"
 #include "utils/float.h"
 #include "utils/formatting.h"
+#include "utils/json.h"
 #include "utils/jsonpath.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
@@ -1629,32 +1630,13 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cx=
t, JsonPathItem *jsp,
 						break;
 					case jbvDatetime:
 						{
-							switch (jb->val.datetime.typid)
-							{
-								case DATEOID:
-									tmp =3D DatumGetCString(DirectFunctionCall1(date_out,
-																			  jb->val.datetime.value));
-									break;
-								case TIMEOID:
-									tmp =3D DatumGetCString(DirectFunctionCall1(time_out,
-																			  jb->val.datetime.value));
-									break;
-								case TIMETZOID:
-									tmp =3D DatumGetCString(DirectFunctionCall1(timetz_out,
-																			  jb->val.datetime.value));
-									break;
-								case TIMESTAMPOID:
-									tmp =3D DatumGetCString(DirectFunctionCall1(timestamp_out,
-																			  jb->val.datetime.value));
-									break;
-								case TIMESTAMPTZOID:
-									tmp =3D DatumGetCString(DirectFunctionCall1(timestamptz_out,
-																			  jb->val.datetime.value));
-									break;
-								default:
-									elog(ERROR, "unrecognized SQL/JSON datetime type oid: %u",
-										 jb->val.datetime.typid);
-							}
+							char		buf[MAXDATELEN + 1];
+
+							JsonEncodeDateTime(buf,
+											   jb->val.datetime.value,
+											   jb->val.datetime.typid,
+											   &jb->val.datetime.tz);
+							tmp =3D pstrdup(buf);
 						}
 						break;
 					case jbvNull:

------- =_aaaaaaaaaa0--