diff options
Diffstat (limited to 'src/backend/utils/adt/jsonpath_exec.c')
| -rw-r--r-- | src/backend/utils/adt/jsonpath_exec.c | 470 |
1 files changed, 447 insertions, 23 deletions
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index 565b00c4266..2738439eb37 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -66,6 +66,7 @@ #include "miscadmin.h" #include "regex/regex.h" #include "utils/builtins.h" +#include "utils/datetime.h" #include "utils/datum.h" #include "utils/formatting.h" #include "utils/float.h" @@ -107,6 +108,7 @@ typedef struct JsonPathExecContext * ignored */ bool throwErrors; /* with "false" all suppressible errors are * suppressed */ + bool useTz; } JsonPathExecContext; /* Context for LIKE_REGEX execution. */ @@ -173,7 +175,8 @@ typedef JsonPathBool (*JsonPathPredicateCallback) (JsonPathItem *jsp, typedef Numeric (*BinaryArithmFunc) (Numeric num1, Numeric num2, bool *error); static JsonPathExecResult executeJsonPath(JsonPath *path, Jsonb *vars, - Jsonb *json, bool throwErrors, JsonValueList *result); + Jsonb *json, bool throwErrors, + JsonValueList *result, bool useTz); static JsonPathExecResult executeItem(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb, JsonValueList *found); static JsonPathExecResult executeItemOptUnwrapTarget(JsonPathExecContext *cxt, @@ -214,6 +217,8 @@ static JsonPathBool executeLikeRegex(JsonPathItem *jsp, JsonbValue *str, static JsonPathExecResult executeNumericItemMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb, bool unwrap, PGFunction func, JsonValueList *found); +static JsonPathExecResult executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonbValue *jb, JsonValueList *found); static JsonPathExecResult executeKeyValueMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, JsonbValue *jb, JsonValueList *found); static JsonPathExecResult appendBoolResult(JsonPathExecContext *cxt, @@ -225,7 +230,8 @@ static void getJsonPathVariable(JsonPathExecContext *cxt, static int JsonbArraySize(JsonbValue *jb); static JsonPathBool executeComparison(JsonPathItem *cmp, JsonbValue *lv, JsonbValue *rv, void *p); -static JsonPathBool compareItems(int32 op, JsonbValue *jb1, JsonbValue *jb2); +static JsonPathBool compareItems(int32 op, JsonbValue *jb1, JsonbValue *jb2, + bool useTz); static int compareNumeric(Numeric a, Numeric b); static JsonbValue *copyJsonbValue(JsonbValue *src); static JsonPathExecResult getArrayIndex(JsonPathExecContext *cxt, @@ -246,6 +252,8 @@ static JsonbValue *JsonbInitBinary(JsonbValue *jbv, Jsonb *jb); static int JsonbType(JsonbValue *jb); static JsonbValue *getScalar(JsonbValue *scalar, enum jbvType type); static JsonbValue *wrapItemsInArray(const JsonValueList *items); +static int compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2, + bool useTz, bool *have_error); /****************** User interface to JsonPath executor ********************/ @@ -261,8 +269,8 @@ static JsonbValue *wrapItemsInArray(const JsonValueList *items); * SQL/JSON. Regarding jsonb_path_match(), this function doesn't have * an analogy in SQL/JSON, so we define its behavior on our own. */ -Datum -jsonb_path_exists(PG_FUNCTION_ARGS) +static Datum +jsonb_path_exists_internal(FunctionCallInfo fcinfo, bool tz) { Jsonb *jb = PG_GETARG_JSONB_P(0); JsonPath *jp = PG_GETARG_JSONPATH_P(1); @@ -276,7 +284,7 @@ jsonb_path_exists(PG_FUNCTION_ARGS) silent = PG_GETARG_BOOL(3); } - res = executeJsonPath(jp, vars, jb, !silent, NULL); + res = executeJsonPath(jp, vars, jb, !silent, NULL, tz); PG_FREE_IF_COPY(jb, 0); PG_FREE_IF_COPY(jp, 1); @@ -287,6 +295,18 @@ jsonb_path_exists(PG_FUNCTION_ARGS) PG_RETURN_BOOL(res == jperOk); } +Datum +jsonb_path_exists(PG_FUNCTION_ARGS) +{ + return jsonb_path_exists_internal(fcinfo, false); +} + +Datum +jsonb_path_exists_tz(PG_FUNCTION_ARGS) +{ + return jsonb_path_exists_internal(fcinfo, true); +} + /* * jsonb_path_exists_opr * Implementation of operator "jsonb @? jsonpath" (2-argument version of @@ -296,7 +316,7 @@ Datum jsonb_path_exists_opr(PG_FUNCTION_ARGS) { /* just call the other one -- it can handle both cases */ - return jsonb_path_exists(fcinfo); + return jsonb_path_exists_internal(fcinfo, false); } /* @@ -304,8 +324,8 @@ jsonb_path_exists_opr(PG_FUNCTION_ARGS) * Returns jsonpath predicate result item for the specified jsonb value. * See jsonb_path_exists() comment for details regarding error handling. */ -Datum -jsonb_path_match(PG_FUNCTION_ARGS) +static Datum +jsonb_path_match_internal(FunctionCallInfo fcinfo, bool tz) { Jsonb *jb = PG_GETARG_JSONB_P(0); JsonPath *jp = PG_GETARG_JSONPATH_P(1); @@ -319,7 +339,7 @@ jsonb_path_match(PG_FUNCTION_ARGS) silent = PG_GETARG_BOOL(3); } - (void) executeJsonPath(jp, vars, jb, !silent, &found); + (void) executeJsonPath(jp, vars, jb, !silent, &found, tz); PG_FREE_IF_COPY(jb, 0); PG_FREE_IF_COPY(jp, 1); @@ -343,6 +363,18 @@ jsonb_path_match(PG_FUNCTION_ARGS) PG_RETURN_NULL(); } +Datum +jsonb_path_match(PG_FUNCTION_ARGS) +{ + return jsonb_path_match_internal(fcinfo, false); +} + +Datum +jsonb_path_match_tz(PG_FUNCTION_ARGS) +{ + return jsonb_path_match_internal(fcinfo, true); +} + /* * jsonb_path_match_opr * Implementation of operator "jsonb @@ jsonpath" (2-argument version of @@ -352,7 +384,7 @@ Datum jsonb_path_match_opr(PG_FUNCTION_ARGS) { /* just call the other one -- it can handle both cases */ - return jsonb_path_match(fcinfo); + return jsonb_path_match_internal(fcinfo, false); } /* @@ -360,8 +392,8 @@ jsonb_path_match_opr(PG_FUNCTION_ARGS) * Executes jsonpath for given jsonb document and returns result as * rowset. */ -Datum -jsonb_path_query(PG_FUNCTION_ARGS) +static Datum +jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz) { FuncCallContext *funcctx; List *found; @@ -385,7 +417,7 @@ jsonb_path_query(PG_FUNCTION_ARGS) vars = PG_GETARG_JSONB_P_COPY(2); silent = PG_GETARG_BOOL(3); - (void) executeJsonPath(jp, vars, jb, !silent, &found); + (void) executeJsonPath(jp, vars, jb, !silent, &found, tz); funcctx->user_fctx = JsonValueListGetList(&found); @@ -406,13 +438,25 @@ jsonb_path_query(PG_FUNCTION_ARGS) SRF_RETURN_NEXT(funcctx, JsonbPGetDatum(JsonbValueToJsonb(v))); } +Datum +jsonb_path_query(PG_FUNCTION_ARGS) +{ + return jsonb_path_query_internal(fcinfo, false); +} + +Datum +jsonb_path_query_tz(PG_FUNCTION_ARGS) +{ + return jsonb_path_query_internal(fcinfo, true); +} + /* * jsonb_path_query_array * Executes jsonpath for given jsonb document and returns result as * jsonb array. */ -Datum -jsonb_path_query_array(PG_FUNCTION_ARGS) +static Datum +jsonb_path_query_array_internal(FunctionCallInfo fcinfo, bool tz) { Jsonb *jb = PG_GETARG_JSONB_P(0); JsonPath *jp = PG_GETARG_JSONPATH_P(1); @@ -420,18 +464,30 @@ jsonb_path_query_array(PG_FUNCTION_ARGS) Jsonb *vars = PG_GETARG_JSONB_P(2); bool silent = PG_GETARG_BOOL(3); - (void) executeJsonPath(jp, vars, jb, !silent, &found); + (void) executeJsonPath(jp, vars, jb, !silent, &found, tz); PG_RETURN_JSONB_P(JsonbValueToJsonb(wrapItemsInArray(&found))); } +Datum +jsonb_path_query_array(PG_FUNCTION_ARGS) +{ + return jsonb_path_query_array_internal(fcinfo, false); +} + +Datum +jsonb_path_query_array_tz(PG_FUNCTION_ARGS) +{ + return jsonb_path_query_array_internal(fcinfo, true); +} + /* * jsonb_path_query_first * Executes jsonpath for given jsonb document and returns first result * item. If there are no items, NULL returned. */ -Datum -jsonb_path_query_first(PG_FUNCTION_ARGS) +static Datum +jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz) { Jsonb *jb = PG_GETARG_JSONB_P(0); JsonPath *jp = PG_GETARG_JSONPATH_P(1); @@ -439,7 +495,7 @@ jsonb_path_query_first(PG_FUNCTION_ARGS) Jsonb *vars = PG_GETARG_JSONB_P(2); bool silent = PG_GETARG_BOOL(3); - (void) executeJsonPath(jp, vars, jb, !silent, &found); + (void) executeJsonPath(jp, vars, jb, !silent, &found, tz); if (JsonValueListLength(&found) >= 1) PG_RETURN_JSONB_P(JsonbValueToJsonb(JsonValueListHead(&found))); @@ -447,6 +503,18 @@ jsonb_path_query_first(PG_FUNCTION_ARGS) PG_RETURN_NULL(); } +Datum +jsonb_path_query_first(PG_FUNCTION_ARGS) +{ + return jsonb_path_query_first_internal(fcinfo, false); +} + +Datum +jsonb_path_query_first_tz(PG_FUNCTION_ARGS) +{ + return jsonb_path_query_first_internal(fcinfo, true); +} + /********************Execute functions for JsonPath**************************/ /* @@ -470,7 +538,7 @@ jsonb_path_query_first(PG_FUNCTION_ARGS) */ static JsonPathExecResult executeJsonPath(JsonPath *path, Jsonb *vars, Jsonb *json, bool throwErrors, - JsonValueList *result) + JsonValueList *result, bool useTz) { JsonPathExecContext cxt; JsonPathExecResult res; @@ -500,6 +568,7 @@ executeJsonPath(JsonPath *path, Jsonb *vars, Jsonb *json, bool throwErrors, cxt.lastGeneratedObjectId = vars ? 2 : 1; cxt.innermostArraySize = -1; cxt.throwErrors = throwErrors; + cxt.useTz = useTz; if (jspStrictAbsenseOfErrors(&cxt) && !result) { @@ -1028,6 +1097,12 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp, } break; + case jpiDatetime: + if (unwrap && JsonbType(jb) == jbvArray) + return executeItemUnwrapTargetArray(cxt, jsp, jb, found, false); + + return executeDateTimeMethod(cxt, jsp, jb, found); + case jpiKeyValue: if (unwrap && JsonbType(jb) == jbvArray) return executeItemUnwrapTargetArray(cxt, jsp, jb, found, false); @@ -1214,7 +1289,7 @@ executeBoolItem(JsonPathExecContext *cxt, JsonPathItem *jsp, jspGetLeftArg(jsp, &larg); jspGetRightArg(jsp, &rarg); return executePredicate(cxt, jsp, &larg, &rarg, jb, true, - executeComparison, NULL); + executeComparison, cxt); case jpiStartsWith: /* 'whole STARTS WITH initial' */ jspGetLeftArg(jsp, &larg); /* 'whole' */ @@ -1694,6 +1769,136 @@ executeNumericItemMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, } /* + * Implementation of the .datetime() method. + * + * Converts a string into a date/time value. The actual type is determined at run time. + * If an argument is provided, this argument is used as a template string. + * Otherwise, the first fitting ISO format is selected. + */ +static JsonPathExecResult +executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, + JsonbValue *jb, JsonValueList *found) +{ + JsonbValue jbvbuf; + Datum value; + text *datetime; + Oid typid; + int32 typmod = -1; + int tz = 0; + bool hasNext; + JsonPathExecResult res = jperNotFound; + JsonPathItem elem; + + if (!(jb = getScalar(jb, jbvString))) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_JSON_DATETIME_FUNCTION), + errmsg("jsonpath item method .%s() can only be applied to a string", + jspOperationName(jsp->type))))); + + datetime = cstring_to_text_with_len(jb->val.string.val, + jb->val.string.len); + + if (jsp->content.arg) + { + text *template; + char *template_str; + int template_len; + bool have_error = false; + + jspGetArg(jsp, &elem); + + if (elem.type != jpiString) + elog(ERROR, "invalid jsonpath item type for .datetime() argument"); + + template_str = jspGetString(&elem, &template_len); + + template = cstring_to_text_with_len(template_str, + template_len); + + value = parse_datetime(datetime, template, true, + &typid, &typmod, &tz, + jspThrowErrors(cxt) ? NULL : &have_error); + + if (have_error) + res = jperError; + else + res = jperOk; + } + else + { + /* + * According to SQL/JSON standard enumerate ISO formats for: date, + * timetz, time, timestamptz, timestamp. + */ + static const char *fmt_str[] = + { + "yyyy-mm-dd", + "HH24:MI:SS TZH:TZM", + "HH24:MI:SS TZH", + "HH24:MI:SS", + "yyyy-mm-dd HH24:MI:SS TZH:TZM", + "yyyy-mm-dd HH24:MI:SS TZH", + "yyyy-mm-dd HH24:MI:SS" + }; + + /* cache for format texts */ + static text *fmt_txt[lengthof(fmt_str)] = {0}; + int i; + + /* loop until datetime format fits */ + for (i = 0; i < lengthof(fmt_str); i++) + { + bool have_error = false; + + if (!fmt_txt[i]) + { + MemoryContext oldcxt = + MemoryContextSwitchTo(TopMemoryContext); + + fmt_txt[i] = cstring_to_text(fmt_str[i]); + MemoryContextSwitchTo(oldcxt); + } + + value = parse_datetime(datetime, fmt_txt[i], true, + &typid, &typmod, &tz, + &have_error); + + if (!have_error) + { + res = jperOk; + break; + } + } + + if (res == jperNotFound) + RETURN_ERROR(ereport(ERROR, + (errcode(ERRCODE_INVALID_ARGUMENT_FOR_JSON_DATETIME_FUNCTION), + errmsg("datetime format is not unrecognized"), + errhint("use datetime template argument for explicit format specification")))); + } + + pfree(datetime); + + if (jperIsError(res)) + return res; + + hasNext = jspGetNext(jsp, &elem); + + if (!hasNext && !found) + return res; + + jb = hasNext ? &jbvbuf : palloc(sizeof(*jb)); + + jb->type = jbvDatetime; + jb->val.datetime.value = value; + jb->val.datetime.typid = typid; + jb->val.datetime.typmod = typmod; + jb->val.datetime.tz = tz; + + return executeNextItem(cxt, jsp, &elem, jb, found, hasNext); +} + +/* * Implementation of .keyvalue() method. * * .keyvalue() method returns a sequence of object's key-value pairs in the @@ -1953,7 +2158,9 @@ JsonbArraySize(JsonbValue *jb) static JsonPathBool executeComparison(JsonPathItem *cmp, JsonbValue *lv, JsonbValue *rv, void *p) { - return compareItems(cmp->type, lv, rv); + JsonPathExecContext *cxt = (JsonPathExecContext *) p; + + return compareItems(cmp->type, lv, rv, cxt->useTz); } /* @@ -2048,7 +2255,7 @@ compareStrings(const char *mbstr1, int mblen1, * Compare two SQL/JSON items using comparison operation 'op'. */ static JsonPathBool -compareItems(int32 op, JsonbValue *jb1, JsonbValue *jb2) +compareItems(int32 op, JsonbValue *jb1, JsonbValue *jb2, bool useTz) { int cmp; bool res; @@ -2089,6 +2296,21 @@ compareItems(int32 op, JsonbValue *jb1, JsonbValue *jb2) cmp = compareStrings(jb1->val.string.val, jb1->val.string.len, jb2->val.string.val, jb2->val.string.len); break; + case jbvDatetime: + { + bool have_error = false; + + cmp = compareDatetime(jb1->val.datetime.value, + jb1->val.datetime.typid, + jb2->val.datetime.value, + jb2->val.datetime.typid, + useTz, + &have_error); + + if (have_error) + return jpbUnknown; + } + break; case jbvBinary: case jbvArray: @@ -2348,3 +2570,205 @@ wrapItemsInArray(const JsonValueList *items) return pushJsonbValue(&ps, WJB_END_ARRAY, NULL); } + +/* + * Cross-type comparison of two datetime SQL/JSON items. If items are + * uncomparable, 'error' flag is set. + */ +static int +compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2, + bool useTz, bool *have_error) +{ + PGFunction cmpfunc = NULL; + + switch (typid1) + { + case DATEOID: + switch (typid2) + { + case DATEOID: + cmpfunc = date_cmp; + + break; + + case TIMESTAMPOID: + val1 = date2timestamp_opt_error(val1, have_error); + if (have_error && *have_error) + return 0; + cmpfunc = timestamp_cmp; + + break; + + case TIMESTAMPTZOID: + if (!useTz) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot convert value from %s to %s without timezone usage", + "date", "timestamptz"), + errhint("use *_tz() function for timezone support"))); + val1 = date2timestamptz_opt_error(val1, have_error); + if (have_error && *have_error) + return 0; + cmpfunc = timestamp_cmp; + + break; + + case TIMEOID: + case TIMETZOID: + *have_error = true; + return 0; + } + break; + + case TIMEOID: + switch (typid2) + { + case TIMEOID: + cmpfunc = time_cmp; + + break; + + case TIMETZOID: + if (!useTz) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot convert value from %s to %s without timezone usage", + "time", "timetz"), + errhint("use *_tz() function for timezone support"))); + val1 = DirectFunctionCall1(time_timetz, val1); + cmpfunc = timetz_cmp; + + break; + + case DATEOID: + case TIMESTAMPOID: + case TIMESTAMPTZOID: + *have_error = true; + return 0; + } + break; + + case TIMETZOID: + switch (typid2) + { + case TIMEOID: + if (!useTz) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot convert value from %s to %s without timezone usage", + "time", "timetz"), + errhint("use *_tz() function for timezone support"))); + val2 = DirectFunctionCall1(time_timetz, val2); + cmpfunc = timetz_cmp; + + break; + + case TIMETZOID: + cmpfunc = timetz_cmp; + + break; + + case DATEOID: + case TIMESTAMPOID: + case TIMESTAMPTZOID: + *have_error = true; + return 0; + } + break; + + case TIMESTAMPOID: + switch (typid2) + { + case DATEOID: + val2 = date2timestamp_opt_error(val2, have_error); + if (have_error && *have_error) + return 0; + cmpfunc = timestamp_cmp; + + break; + + case TIMESTAMPOID: + cmpfunc = timestamp_cmp; + + break; + + case TIMESTAMPTZOID: + if (!useTz) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot convert value from %s to %s without timezone usage", + "timestamp", "timestamptz"), + errhint("use *_tz() function for timezone support"))); + val1 = timestamp2timestamptz_opt_error(val1, have_error); + if (have_error && *have_error) + return 0; + cmpfunc = timestamp_cmp; + + break; + + case TIMEOID: + case TIMETZOID: + *have_error = true; + return 0; + } + break; + + case TIMESTAMPTZOID: + switch (typid2) + { + case DATEOID: + if (!useTz) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot convert value from %s to %s without timezone usage", + "date", "timestamptz"), + errhint("use *_tz() function for timezone support"))); + val2 = date2timestamptz_opt_error(val2, have_error); + if (have_error && *have_error) + return 0; + cmpfunc = timestamp_cmp; + + break; + + case TIMESTAMPOID: + if (!useTz) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot convert value from %s to %s without timezone usage", + "timestamp", "timestamptz"), + errhint("use *_tz() function for timezone support"))); + val2 = timestamp2timestamptz_opt_error(val2, have_error); + if (have_error && *have_error) + return 0; + cmpfunc = timestamp_cmp; + + break; + + case TIMESTAMPTZOID: + cmpfunc = timestamp_cmp; + + break; + + case TIMEOID: + case TIMETZOID: + *have_error = true; + return 0; + } + break; + + default: + elog(ERROR, "unrecognized SQL/JSON datetime type oid: %d", + typid1); + } + + if (*have_error) + return 0; + + if (!cmpfunc) + elog(ERROR, "unrecognized SQL/JSON datetime type oid: %d", + typid2); + + *have_error = false; + + return DatumGetInt32(DirectFunctionCall2(cmpfunc, val1, val2)); +} |