Make jsonpath .string() be immutable for datetimes.

Discussion of commit ed055d249 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 66ea94e8e).  Also
back-patch ed055d249 to provide test cases.

Discussion: https://postgr.es/m/5e8879d0-a3c8-4be2-950f-d83aa2af953a@eisentraut.org

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6b2759e25a15978c208faf7d7deecf5b070d0c69..b51dcfd2b4bdcc6dfad958bc4908e4352c128922 100644 (file)
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17965,15 +17965,16 @@ ERROR:  jsonpath member accessor can only be applied to an object
         <returnvalue><replaceable>string</replaceable></returnvalue>
        </para>
        <para>
-        String value converted from a JSON boolean, number, string, or datetime
+        String value converted from a JSON boolean, number, string, or
+        datetime
        </para>
        <para>
         <literal>jsonb_path_query_array('[1.23, "xyz", false]', '$[*].string()')</literal>
         <returnvalue>["1.23", "xyz", "false"]</returnvalue>
        </para>
        <para>
-        <literal>jsonb_path_query('"2023-08-15"', '$.datetime().string()')</literal>
-        <returnvalue>"2023-08-15"</returnvalue>
+        <literal>jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()')</literal>
+        <returnvalue>"2023-08-15T12:34:56"</returnvalue>
        </para></entry>
       </row>
 
@@ -18054,7 +18055,9 @@ ERROR:  jsonpath member accessor can only be applied to an object
         <returnvalue><replaceable>decimal</replaceable></returnvalue>
        </para>
        <para>
-        Rounded decimal value converted from a JSON number or string. <literal>precision</literal> and <literal>scale</literal> must be integer values.
+        Rounded decimal value converted from a JSON number or string
+        (<literal>precision</literal> and <literal>scale</literal> must be
+        integer values)
        </para>
        <para>
         <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
        </para>
        <para>
         Time without time zone value converted from a string, with fractional
-        seconds adjusted to the given precision.
+        seconds adjusted to the given precision
        </para>
        <para>
         <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
        </para>
        <para>
         Time with time zone value converted from a string, with fractional
-        seconds adjusted to the given precision.
+        seconds adjusted to the given precision
        </para>
        <para>
         <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
        </para>
        <para>
         Timestamp without time zone value converted from a string, with
-        fractional seconds adjusted to the given precision.
+        fractional seconds adjusted to the given precision
        </para>
        <para>
         <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
        </para>
        <para>
         Timestamp with time zone value converted from a string, with fractional
-        seconds adjusted to the given precision.
+        seconds adjusted to the given precision
        </para>
        <para>
         <literal>jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2)')</literal>

diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index e569c7efb83cdf8ca7212c4af0070d35cd7bde9d..1184cba983af06ef6e96084de77fc2604b8b5da1 100644 (file)
--- 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 *cxt, JsonPathItem *jsp,
                        break;
                    case jbvDatetime:
                        {
-                           switch (jb->val.datetime.typid)
-                           {
-                               case DATEOID:
-                                   tmp = DatumGetCString(DirectFunctionCall1(date_out,
-                                                                             jb->val.datetime.value));
-                                   break;
-                               case TIMEOID:
-                                   tmp = DatumGetCString(DirectFunctionCall1(time_out,
-                                                                             jb->val.datetime.value));
-                                   break;
-                               case TIMETZOID:
-                                   tmp = DatumGetCString(DirectFunctionCall1(timetz_out,
-                                                                             jb->val.datetime.value));
-                                   break;
-                               case TIMESTAMPOID:
-                                   tmp = DatumGetCString(DirectFunctionCall1(timestamp_out,
-                                                                             jb->val.datetime.value));
-                                   break;
-                               case TIMESTAMPTZOID:
-                                   tmp = 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 = pstrdup(buf);
                        }
                        break;
                    case jbvNull:

diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 241713cc51e63b382c7d634c86396ac23cfb1812..58da26ab0e1dc38a761fc8201861532eef4c5070 100644 (file)
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -1,13 +1,18 @@
 --
 -- HOROLOGY
 --
-SET DateStyle = 'Postgres, MDY';
-SHOW TimeZone;  -- Many of these tests depend on the prevailing setting
+SHOW TimeZone;  -- Many of these tests depend on the prevailing settings
  TimeZone 
 ----------
  PST8PDT
 (1 row)
 
+SHOW DateStyle;
+   DateStyle   
+---------------
+ Postgres, MDY
+(1 row)
+
 --
 -- Test various input formats
 --

diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index b5bcece94e39aa855df4f9a3493237e8c99bf757..57c117ea5806441119a8449e06f63029b61de7bd 100644 (file)
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2586,15 +2586,6 @@ select jsonb_path_query('[2, true]', '$.string()');
  "true"
 (2 rows)
 
-select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
-ERROR:  cannot convert value from timestamptz to timestamp without time zone usage
-HINT:  Use *_tz() function for time zone support.
-select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
-    jsonb_path_query_tz     
-----------------------------
- "Tue Aug 15 00:04:56 2023"
-(1 row)
-
 select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
   jsonb_path_query_array  
 --------------------------
@@ -2607,6 +2598,77 @@ select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
  ["string", "string", "string"]
 (1 row)
 
+select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
+ERROR:  cannot convert value from timestamptz to timestamp without time zone usage
+HINT:  Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
+  jsonb_path_query_tz  
+-----------------------
+ "2023-08-15T00:04:56"
+(1 row)
+
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
+ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
+HINT:  Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()'); -- should work
+     jsonb_path_query_tz     
+-----------------------------
+ "2023-08-15T12:34:56-07:00"
+(1 row)
+
+select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
+      jsonb_path_query       
+-----------------------------
+ "2023-08-15T12:34:56+05:30"
+(1 row)
+
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
+   jsonb_path_query    
+-----------------------
+ "2023-08-15T12:34:56"
+(1 row)
+
+select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
+ jsonb_path_query 
+------------------
+ "12:34:56+05:30"
+(1 row)
+
+select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
+ jsonb_path_query_tz 
+---------------------
+ "12:34:56-07:00"
+(1 row)
+
+select jsonb_path_query('"12:34:56"', '$.time().string()');
+ jsonb_path_query 
+------------------
+ "12:34:56"
+(1 row)
+
+select jsonb_path_query('"2023-08-15"', '$.date().string()');
+ jsonb_path_query 
+------------------
+ "2023-08-15"
+(1 row)
+
+-- .string() does not react to timezone or datestyle
+begin;
+set local timezone = 'UTC';
+set local datestyle = 'German';
+select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
+      jsonb_path_query       
+-----------------------------
+ "2023-08-15T12:34:56+05:30"
+(1 row)
+
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
+   jsonb_path_query    
+-----------------------
+ "2023-08-15T12:34:56"
+(1 row)
+
+rollback;
 -- Test .time()
 select jsonb_path_query('null', '$.time()');
 ERROR:  jsonpath item method .time() can only be applied to a string

diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index e5cf12ff63d33e76ee0cf580c81807982e706643..0fe3c783e618138d7bcd1b4898f8ead7ee0f9e74 100644 (file)
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -1,9 +1,9 @@
 --
 -- HOROLOGY
 --
-SET DateStyle = 'Postgres, MDY';
 
-SHOW TimeZone;  -- Many of these tests depend on the prevailing setting
+SHOW TimeZone;  -- Many of these tests depend on the prevailing settings
+SHOW DateStyle;
 
 --
 -- Test various input formats

diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 5e14f7759bb64cc51d3acbf7798ed3be968f60d5..c647af55e9412daba75cbc044a0bcffd306be754 100644 (file)
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -587,10 +587,26 @@ select jsonb_path_query('1234', '$.string()');
 select jsonb_path_query('true', '$.string()');
 select jsonb_path_query('1234', '$.string().type()');
 select jsonb_path_query('[2, true]', '$.string()');
-select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
-select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
 select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
 select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
+select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()'); -- should work
+select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
+select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
+select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
+select jsonb_path_query('"12:34:56"', '$.time().string()');
+select jsonb_path_query('"2023-08-15"', '$.date().string()');
+
+-- .string() does not react to timezone or datestyle
+begin;
+set local timezone = 'UTC';
+set local datestyle = 'German';
+select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
+rollback;
 
 -- Test .time()
 select jsonb_path_query('null', '$.time()');