🌐 AI搜索 & 代理 主页
Skip to content

Commit cb599b9

Browse files
tglsfdctglsfdc
committed
Make jsonpath .string() be immutable for datetimes.
Discussion of commit ed055d2 revealed that we don't actually want jsonpath's .string() method to depend on DateStyle, nor TimeZone either, because the non-"_tz" jsonpath functions are supposed to be immutable. Potentially we could allow a TimeZone dependency in the "_tz" variants, but it seems better to just uniformly define this method as returning the same string that jsonb text output would do. That's easier to implement too, saving a couple dozen lines. Patch by me, per complaint from Peter Eisentraut. Back-patch to v17 where this feature came in (in 66ea94e). Also back-patch ed055d2 to provide test cases. Discussion: https://postgr.es/m/5e8879d0-a3c8-4be2-950f-d83aa2af953a@eisentraut.org
1 parent 4eada20 commit cb599b9

File tree

4 files changed

+39
-87
lines changed

4 files changed

+39
-87
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -18017,16 +18017,15 @@ ERROR: jsonpath member accessor can only be applied to an object
1801718017
</para>
1801818018
<para>
1801918019
String value converted from a JSON boolean, number, string, or
18020-
datetime (the output format for datetimes is determined by
18021-
the <xref linkend="guc-datestyle"/> parameter)
18020+
datetime
1802218021
</para>
1802318022
<para>
1802418023
<literal>jsonb_path_query_array('[1.23, "xyz", false]', '$[*].string()')</literal>
1802518024
<returnvalue>["1.23", "xyz", "false"]</returnvalue>
1802618025
</para>
1802718026
<para>
18028-
<literal>jsonb_path_query('"2023-08-15"', '$.datetime().string()')</literal>
18029-
<returnvalue>"2023-08-15"</returnvalue>
18027+
<literal>jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()')</literal>
18028+
<returnvalue>"2023-08-15T12:34:56"</returnvalue>
1803018029
</para></entry>
1803118030
</row>
1803218031

‎src/backend/utils/adt/jsonpath_exec.c‎

Lines changed: 8 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -72,6 +72,7 @@
7272
#include "utils/datetime.h"
7373
#include "utils/float.h"
7474
#include "utils/formatting.h"
75+
#include "utils/json.h"
7576
#include "utils/jsonpath.h"
7677
#include "utils/lsyscache.h"
7778
#include "utils/memutils.h"
@@ -1629,32 +1630,13 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
16291630
break;
16301631
case jbvDatetime:
16311632
{
1632-
switch (jb->val.datetime.typid)
1633-
{
1634-
case DATEOID:
1635-
tmp = DatumGetCString(DirectFunctionCall1(date_out,
1636-
jb->val.datetime.value));
1637-
break;
1638-
case TIMEOID:
1639-
tmp = DatumGetCString(DirectFunctionCall1(time_out,
1640-
jb->val.datetime.value));
1641-
break;
1642-
case TIMETZOID:
1643-
tmp = DatumGetCString(DirectFunctionCall1(timetz_out,
1644-
jb->val.datetime.value));
1645-
break;
1646-
case TIMESTAMPOID:
1647-
tmp = DatumGetCString(DirectFunctionCall1(timestamp_out,
1648-
jb->val.datetime.value));
1649-
break;
1650-
case TIMESTAMPTZOID:
1651-
tmp = DatumGetCString(DirectFunctionCall1(timestamptz_out,
1652-
jb->val.datetime.value));
1653-
break;
1654-
default:
1655-
elog(ERROR, "unrecognized SQL/JSON datetime type oid: %u",
1656-
jb->val.datetime.typid);
1657-
}
1633+
char buf[MAXDATELEN + 1];
1634+
1635+
JsonEncodeDateTime(buf,
1636+
jb->val.datetime.value,
1637+
jb->val.datetime.typid,
1638+
&jb->val.datetime.tz);
1639+
tmp = pstrdup(buf);
16581640
}
16591641
break;
16601642
case jbvNull:

‎src/test/regress/expected/jsonb_jsonpath.out‎

Lines changed: 23 additions & 50 deletions
Original file line numberDiff line numberDiff line change
@@ -2652,30 +2652,30 @@ select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()')
26522652
ERROR: cannot convert value from timestamptz to timestamp without time zone usage
26532653
HINT: Use *_tz() function for time zone support.
26542654
select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
2655-
jsonb_path_query_tz
2656-
----------------------------
2657-
"Tue Aug 15 00:04:56 2023"
2655+
jsonb_path_query_tz
2656+
-----------------------
2657+
"2023-08-15T00:04:56"
26582658
(1 row)
26592659

26602660
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
26612661
ERROR: cannot convert value from timestamp to timestamptz without time zone usage
26622662
HINT: Use *_tz() function for time zone support.
26632663
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()'); -- should work
2664-
jsonb_path_query_tz
2665-
--------------------------------
2666-
"Tue Aug 15 12:34:56 2023 PDT"
2664+
jsonb_path_query_tz
2665+
-----------------------------
2666+
"2023-08-15T12:34:56-07:00"
26672667
(1 row)
26682668

26692669
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
2670-
jsonb_path_query
2671-
--------------------------------
2672-
"Tue Aug 15 00:04:56 2023 PDT"
2670+
jsonb_path_query
2671+
-----------------------------
2672+
"2023-08-15T12:34:56+05:30"
26732673
(1 row)
26742674

26752675
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
2676-
jsonb_path_query
2677-
----------------------------
2678-
"Tue Aug 15 12:34:56 2023"
2676+
jsonb_path_query
2677+
-----------------------
2678+
"2023-08-15T12:34:56"
26792679
(1 row)
26802680

26812681
select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
@@ -2687,7 +2687,7 @@ select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
26872687
select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
26882688
jsonb_path_query_tz
26892689
---------------------
2690-
"12:34:56-07"
2690+
"12:34:56-07:00"
26912691
(1 row)
26922692

26932693
select jsonb_path_query('"12:34:56"', '$.time().string()');
@@ -2699,53 +2699,26 @@ select jsonb_path_query('"12:34:56"', '$.time().string()');
26992699
select jsonb_path_query('"2023-08-15"', '$.date().string()');
27002700
jsonb_path_query
27012701
------------------
2702-
"08-15-2023"
2703-
(1 row)
2704-
2705-
set datestyle = 'ISO';
2706-
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
2707-
jsonb_path_query_tz
2708-
--------------------------
2709-
"2023-08-15 12:34:56-07"
2702+
"2023-08-15"
27102703
(1 row)
27112704

2705+
-- .string() does not react to timezone or datestyle
2706+
begin;
2707+
set local timezone = 'UTC';
2708+
set local datestyle = 'German';
27122709
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
2713-
jsonb_path_query
2714-
--------------------------
2715-
"2023-08-15 00:04:56-07"
2710+
jsonb_path_query
2711+
-----------------------------
2712+
"2023-08-15T12:34:56+05:30"
27162713
(1 row)
27172714

27182715
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
27192716
jsonb_path_query
27202717
-----------------------
2721-
"2023-08-15 12:34:56"
2722-
(1 row)
2723-
2724-
select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
2725-
jsonb_path_query
2726-
------------------
2727-
"12:34:56+05:30"
2728-
(1 row)
2729-
2730-
select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
2731-
jsonb_path_query_tz
2732-
---------------------
2733-
"12:34:56-07"
2734-
(1 row)
2735-
2736-
select jsonb_path_query('"12:34:56"', '$.time().string()');
2737-
jsonb_path_query
2738-
------------------
2739-
"12:34:56"
2740-
(1 row)
2741-
2742-
select jsonb_path_query('"2023-08-15"', '$.date().string()');
2743-
jsonb_path_query
2744-
------------------
2745-
"2023-08-15"
2718+
"2023-08-15T12:34:56"
27462719
(1 row)
27472720

2748-
reset datestyle;
2721+
rollback;
27492722
-- Test .time()
27502723
select jsonb_path_query('null', '$.time()');
27512724
ERROR: jsonpath item method .time() can only be applied to a string

‎src/test/regress/sql/jsonb_jsonpath.sql‎

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -611,15 +611,13 @@ select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
611611
select jsonb_path_query('"12:34:56"', '$.time().string()');
612612
select jsonb_path_query('"2023-08-15"', '$.date().string()');
613613

614-
set datestyle = 'ISO';
615-
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
614+
-- .string() does not react to timezone or datestyle
615+
begin;
616+
set local timezone = 'UTC';
617+
set local datestyle = 'German';
616618
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
617619
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
618-
select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
619-
select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
620-
select jsonb_path_query('"12:34:56"', '$.time().string()');
621-
select jsonb_path_query('"2023-08-15"', '$.date().string()');
622-
reset datestyle;
620+
rollback;
623621

624622
-- Test .time()
625623
select jsonb_path_query('null', '$.time()');

0 commit comments

Comments
 (0)