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

Commitcc4fdfa

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 parent2645f6d commitcc4fdfa

File tree

6 files changed

+117
-49
lines changed

6 files changed

+117
-49
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 11 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -17965,15 +17965,16 @@ ERROR: jsonpath member accessor can only be applied to an object
1796517965
<returnvalue><replaceable>string</replaceable></returnvalue>
1796617966
</para>
1796717967
<para>
17968-
String value converted from a JSON boolean, number, string, or datetime
17968+
String value converted from a JSON boolean, number, string, or
17969+
datetime
1796917970
</para>
1797017971
<para>
1797117972
<literal>jsonb_path_query_array('[1.23, "xyz", false]', '$[*].string()')</literal>
1797217973
<returnvalue>["1.23", "xyz", "false"]</returnvalue>
1797317974
</para>
1797417975
<para>
17975-
<literal>jsonb_path_query('"2023-08-15"', '$.datetime().string()')</literal>
17976-
<returnvalue>"2023-08-15"</returnvalue>
17976+
<literal>jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()')</literal>
17977+
<returnvalue>"2023-08-15T12:34:56"</returnvalue>
1797717978
</para></entry>
1797817979
</row>
1797917980

@@ -18054,7 +18055,9 @@ ERROR: jsonpath member accessor can only be applied to an object
1805418055
<returnvalue><replaceable>decimal</replaceable></returnvalue>
1805518056
</para>
1805618057
<para>
18057-
Rounded decimal value converted from a JSON number or string. <literal>precision</literal> and <literal>scale</literal> must be integer values.
18058+
Rounded decimal value converted from a JSON number or string
18059+
(<literal>precision</literal> and <literal>scale</literal> must be
18060+
integer values)
1805818061
</para>
1805918062
<para>
1806018063
<literal>jsonb_path_query('1234.5678', '$.decimal(6, 2)')</literal>
@@ -18156,7 +18159,7 @@ ERROR: jsonpath member accessor can only be applied to an object
1815618159
</para>
1815718160
<para>
1815818161
Time without time zone value converted from a string, with fractional
18159-
seconds adjusted to the given precision.
18162+
seconds adjusted to the given precision
1816018163
</para>
1816118164
<para>
1816218165
<literal>jsonb_path_query('"12:34:56.789"', '$.time(2)')</literal>
@@ -18185,7 +18188,7 @@ ERROR: jsonpath member accessor can only be applied to an object
1818518188
</para>
1818618189
<para>
1818718190
Time with time zone value converted from a string, with fractional
18188-
seconds adjusted to the given precision.
18191+
seconds adjusted to the given precision
1818918192
</para>
1819018193
<para>
1819118194
<literal>jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2)')</literal>
@@ -18214,7 +18217,7 @@ ERROR: jsonpath member accessor can only be applied to an object
1821418217
</para>
1821518218
<para>
1821618219
Timestamp without time zone value converted from a string, with
18217-
fractional seconds adjusted to the given precision.
18220+
fractional seconds adjusted to the given precision
1821818221
</para>
1821918222
<para>
1822018223
<literal>jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2)')</literal>
@@ -18243,7 +18246,7 @@ ERROR: jsonpath member accessor can only be applied to an object
1824318246
</para>
1824418247
<para>
1824518248
Timestamp with time zone value converted from a string, with fractional
18246-
seconds adjusted to the given precision.
18249+
seconds adjusted to the given precision
1824718250
</para>
1824818251
<para>
1824918252
<literal>jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2)')</literal>

‎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/horology.out

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,18 @@
11
--
22
-- HOROLOGY
33
--
4-
SET DateStyle = 'Postgres, MDY';
5-
SHOW TimeZone; -- Many of these tests depend on the prevailing setting
4+
SHOW TimeZone; -- Many of these tests depend on the prevailing settings
65
TimeZone
76
----------
87
PST8PDT
98
(1 row)
109

10+
SHOW DateStyle;
11+
DateStyle
12+
---------------
13+
Postgres, MDY
14+
(1 row)
15+
1116
--
1217
-- Test various input formats
1318
--

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

Lines changed: 71 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -2586,15 +2586,6 @@ select jsonb_path_query('[2, true]', '$.string()');
25862586
"true"
25872587
(2 rows)
25882588

2589-
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
2590-
ERROR: cannot convert value from timestamptz to timestamp without time zone usage
2591-
HINT: Use *_tz() function for time zone support.
2592-
select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
2593-
jsonb_path_query_tz
2594-
----------------------------
2595-
"Tue Aug 15 00:04:56 2023"
2596-
(1 row)
2597-
25982589
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
25992590
jsonb_path_query_array
26002591
--------------------------
@@ -2607,6 +2598,77 @@ select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
26072598
["string", "string", "string"]
26082599
(1 row)
26092600

2601+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
2602+
ERROR: cannot convert value from timestamptz to timestamp without time zone usage
2603+
HINT: Use *_tz() function for time zone support.
2604+
select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
2605+
jsonb_path_query_tz
2606+
-----------------------
2607+
"2023-08-15T00:04:56"
2608+
(1 row)
2609+
2610+
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
2611+
ERROR: cannot convert value from timestamp to timestamptz without time zone usage
2612+
HINT: Use *_tz() function for time zone support.
2613+
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()'); -- should work
2614+
jsonb_path_query_tz
2615+
-----------------------------
2616+
"2023-08-15T12:34:56-07:00"
2617+
(1 row)
2618+
2619+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
2620+
jsonb_path_query
2621+
-----------------------------
2622+
"2023-08-15T12:34:56+05:30"
2623+
(1 row)
2624+
2625+
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
2626+
jsonb_path_query
2627+
-----------------------
2628+
"2023-08-15T12:34:56"
2629+
(1 row)
2630+
2631+
select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
2632+
jsonb_path_query
2633+
------------------
2634+
"12:34:56+05:30"
2635+
(1 row)
2636+
2637+
select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
2638+
jsonb_path_query_tz
2639+
---------------------
2640+
"12:34:56-07:00"
2641+
(1 row)
2642+
2643+
select jsonb_path_query('"12:34:56"', '$.time().string()');
2644+
jsonb_path_query
2645+
------------------
2646+
"12:34:56"
2647+
(1 row)
2648+
2649+
select jsonb_path_query('"2023-08-15"', '$.date().string()');
2650+
jsonb_path_query
2651+
------------------
2652+
"2023-08-15"
2653+
(1 row)
2654+
2655+
-- .string() does not react to timezone or datestyle
2656+
begin;
2657+
set local timezone = 'UTC';
2658+
set local datestyle = 'German';
2659+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
2660+
jsonb_path_query
2661+
-----------------------------
2662+
"2023-08-15T12:34:56+05:30"
2663+
(1 row)
2664+
2665+
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
2666+
jsonb_path_query
2667+
-----------------------
2668+
"2023-08-15T12:34:56"
2669+
(1 row)
2670+
2671+
rollback;
26102672
-- Test .time()
26112673
select jsonb_path_query('null', '$.time()');
26122674
ERROR: jsonpath item method .time() can only be applied to a string

‎src/test/regress/sql/horology.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
--
22
-- HOROLOGY
33
--
4-
SET DateStyle='Postgres, MDY';
54

6-
SHOW TimeZone;-- Many of these tests depend on the prevailing setting
5+
SHOW TimeZone;-- Many of these tests depend on the prevailing settings
6+
SHOW DateStyle;
77

88
--
99
-- Test various input formats

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

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -587,10 +587,26 @@ select jsonb_path_query('1234', '$.string()');
587587
select jsonb_path_query('true','$.string()');
588588
select jsonb_path_query('1234','$.string().type()');
589589
select jsonb_path_query('[2, true]','$.string()');
590-
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"','$.timestamp().string()');
591-
select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"','$.timestamp().string()');-- should work
592590
select jsonb_path_query_array('[1.23, "yes", false]','$[*].string()');
593591
select jsonb_path_query_array('[1.23, "yes", false]','$[*].string().type()');
592+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"','$.timestamp().string()');
593+
select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"','$.timestamp().string()');-- should work
594+
select jsonb_path_query('"2023-08-15 12:34:56"','$.timestamp_tz().string()');
595+
select jsonb_path_query_tz('"2023-08-15 12:34:56"','$.timestamp_tz().string()');-- should work
596+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"','$.timestamp_tz().string()');
597+
select jsonb_path_query('"2023-08-15 12:34:56"','$.timestamp().string()');
598+
select jsonb_path_query('"12:34:56 +5:30"','$.time_tz().string()');
599+
select jsonb_path_query_tz('"12:34:56"','$.time_tz().string()');
600+
select jsonb_path_query('"12:34:56"','$.time().string()');
601+
select jsonb_path_query('"2023-08-15"','$.date().string()');
602+
603+
-- .string() does not react to timezone or datestyle
604+
begin;
605+
set local timezone='UTC';
606+
set local datestyle='German';
607+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"','$.timestamp_tz().string()');
608+
select jsonb_path_query('"2023-08-15 12:34:56"','$.timestamp().string()');
609+
rollback;
594610

595611
-- Test .time()
596612
select jsonb_path_query('null','$.time()');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp