Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcb599b9

Browse files
committed
Make jsonpath .string() be immutable for datetimes.
Discussion of commited055d2 revealed that we don't actuallywant jsonpath's .string() method to depend on DateStyle, norTimeZone either, because the non-"_tz" jsonpath functions aresupposed to be immutable. Potentially we could allow a TimeZonedependency in the "_tz" variants, but it seems better to justuniformly define this method as returning the same string thatjsonb text output would do. That's easier to implement too,saving a couple dozen lines.Patch by me, per complaint from Peter Eisentraut. Back-patchto v17 where this feature came in (in66ea94e). Alsoback-patched055d2 to provide test cases.Discussion:https://postgr.es/m/5e8879d0-a3c8-4be2-950f-d83aa2af953a@eisentraut.org
1 parent4eada20 commitcb599b9

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
casejbvDatetime:
16311632
{
1632-
switch (jb->val.datetime.typid)
1633-
{
1634-
caseDATEOID:
1635-
tmp=DatumGetCString(DirectFunctionCall1(date_out,
1636-
jb->val.datetime.value));
1637-
break;
1638-
caseTIMEOID:
1639-
tmp=DatumGetCString(DirectFunctionCall1(time_out,
1640-
jb->val.datetime.value));
1641-
break;
1642-
caseTIMETZOID:
1643-
tmp=DatumGetCString(DirectFunctionCall1(timetz_out,
1644-
jb->val.datetime.value));
1645-
break;
1646-
caseTIMESTAMPOID:
1647-
tmp=DatumGetCString(DirectFunctionCall1(timestamp_out,
1648-
jb->val.datetime.value));
1649-
break;
1650-
caseTIMESTAMPTZOID:
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+
charbuf[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
casejbvNull:

‎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)
[8]ページ先頭
©2009-2025 Movatter.jp