From d02fea59799aceaf95dd81b8f2416434788e04c0 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Thu, 25 Aug 2022 02:42:17 +0300 Subject: [PATCH v8] WIP: Remove subtransactions in JsonExpr execution --- src/backend/executor/execExpr.c | 84 +- src/backend/executor/execExprInterp.c | 892 ++++++++++++-------- src/backend/jit/llvm/llvmjit_expr.c | 2 +- src/backend/jit/llvm/llvmjit_types.c | 2 +- src/backend/nodes/nodeFuncs.c | 91 +- src/backend/optimizer/util/clauses.c | 12 - src/backend/parser/parse_expr.c | 200 ++--- src/backend/utils/adt/bool.c | 31 +- src/backend/utils/adt/date.c | 158 +++- src/backend/utils/adt/jsonb.c | 26 +- src/backend/utils/adt/numeric.c | 64 +- src/backend/utils/adt/numutils.c | 20 +- src/backend/utils/adt/timestamp.c | 27 +- src/include/executor/execExpr.h | 49 +- src/include/nodes/primnodes.h | 36 +- src/include/utils/builtins.h | 2 + src/include/utils/date.h | 10 + src/include/utils/jsonb.h | 2 + src/include/utils/numeric.h | 4 +- src/include/utils/timestamp.h | 2 + src/test/regress/expected/jsonb_sqljson.out | 330 ++++++-- src/test/regress/sql/jsonb_sqljson.sql | 76 +- 22 files changed, 1244 insertions(+), 876 deletions(-) diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index d0a57c7aaee..558992df909 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -2561,58 +2561,41 @@ ExecInitExprRec(Expr *node, ExprState *state, case T_JsonExpr: { JsonExpr *jexpr = castNode(JsonExpr, node); - JsonExprState *jsestate = palloc0(sizeof(JsonExprState)); + JsonExprState *jsestate; ListCell *argexprlc; ListCell *argnamelc; - scratch.opcode = EEOP_JSONEXPR; - scratch.d.jsonexpr.jsestate = jsestate; + /* JSON_TABLE preudo-function returns context item as a result */ + if (jexpr->op == JSON_TABLE_OP) + { + ExecInitExprRec((Expr *) jexpr->formatted_expr, state, + resv, resnull); + break; + } + jsestate = palloc0(sizeof(JsonExprState)); jsestate->jsexpr = jexpr; - jsestate->formatted_expr = - palloc(sizeof(*jsestate->formatted_expr)); + scratch.opcode = EEOP_JSONEXPR; + scratch.d.jsonexpr.jsestate = jsestate; ExecInitExprRec((Expr *) jexpr->formatted_expr, state, - &jsestate->formatted_expr->value, - &jsestate->formatted_expr->isnull); - - jsestate->pathspec = - palloc(sizeof(*jsestate->pathspec)); + &jsestate->formatted_expr.value, + &jsestate->formatted_expr.isnull); ExecInitExprRec((Expr *) jexpr->path_spec, state, - &jsestate->pathspec->value, - &jsestate->pathspec->isnull); - - jsestate->res_expr = - palloc(sizeof(*jsestate->res_expr)); - - jsestate->result_expr = jexpr->result_coercion - ? ExecInitExprWithCaseValue((Expr *) jexpr->result_coercion->expr, - state->parent, - &jsestate->res_expr->value, - &jsestate->res_expr->isnull) - : NULL; + &jsestate->pathspec.value, + &jsestate->pathspec.isnull); - jsestate->default_on_empty = !jexpr->on_empty ? NULL : - ExecInitExpr((Expr *) jexpr->on_empty->default_expr, - state->parent); + if (jexpr->on_empty) + jsestate->default_on_empty = + ExecInitExpr((Expr *) jexpr->on_empty->default_expr, + state->parent); jsestate->default_on_error = ExecInitExpr((Expr *) jexpr->on_error->default_expr, state->parent); - if (jexpr->omit_quotes || - (jexpr->result_coercion && jexpr->result_coercion->via_io)) - { - Oid typinput; - - /* lookup the result type's input function */ - getTypeInputInfo(jexpr->returning->typid, &typinput, - &jsestate->input.typioparam); - fmgr_info(typinput, &jsestate->input.func); - } - jsestate->args = NIL; forboth(argexprlc, jexpr->passing_values, @@ -2636,35 +2619,8 @@ ExecInitExprRec(Expr *node, ExprState *state, lappend(jsestate->args, var); } - jsestate->cache = NULL; - - if (jexpr->coercions) - { - JsonCoercion **coercion; - struct JsonCoercionState *cstate; - Datum *caseval; - bool *casenull; - - jsestate->coercion_expr = - palloc(sizeof(*jsestate->coercion_expr)); - - caseval = &jsestate->coercion_expr->value; - casenull = &jsestate->coercion_expr->isnull; - - for (cstate = &jsestate->coercions.null, - coercion = &jexpr->coercions->null; - coercion <= &jexpr->coercions->composite; - coercion++, cstate++) - { - cstate->coercion = *coercion; - cstate->estate = *coercion ? - ExecInitExprWithCaseValue((Expr *) (*coercion)->expr, - state->parent, - caseval, casenull) : NULL; - } - } - ExprEvalPushStep(state, &scratch); + break; } diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 636794ca6f1..b2accedf8ca 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -1844,7 +1844,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_CASE(EEOP_JSONEXPR) { /* too complex for an inline implementation */ - ExecEvalJson(state, op, econtext); + ExecEvalJsonExpr(state, op, econtext); EEO_NEXT(); } @@ -4700,96 +4700,6 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op, *op->resnull = isnull; } -/* - * Evaluate a JSON error/empty behavior result. - */ -static Datum -ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior, - ExprState *default_estate, bool *is_null) -{ - *is_null = false; - - switch (behavior->btype) - { - case JSON_BEHAVIOR_EMPTY_ARRAY: - return JsonbPGetDatum(JsonbMakeEmptyArray()); - - case JSON_BEHAVIOR_EMPTY_OBJECT: - return JsonbPGetDatum(JsonbMakeEmptyObject()); - - case JSON_BEHAVIOR_TRUE: - return BoolGetDatum(true); - - case JSON_BEHAVIOR_FALSE: - return BoolGetDatum(false); - - case JSON_BEHAVIOR_NULL: - case JSON_BEHAVIOR_UNKNOWN: - case JSON_BEHAVIOR_EMPTY: - *is_null = true; - return (Datum) 0; - - case JSON_BEHAVIOR_DEFAULT: - return ExecEvalExpr(default_estate, econtext, is_null); - - default: - elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype); - return (Datum) 0; - } -} - -/* - * Evaluate a coercion of a JSON item to the target type. - */ -static Datum -ExecEvalJsonExprCoercion(ExprEvalStep *op, ExprContext *econtext, - Datum res, bool *isNull, void *p, bool *error) -{ - ExprState *estate = p; - JsonExprState *jsestate; - - if (estate) /* coerce using specified expression */ - return ExecEvalExpr(estate, econtext, isNull); - - jsestate = op->d.jsonexpr.jsestate; - - if (jsestate->jsexpr->op != JSON_EXISTS_OP) - { - JsonCoercion *coercion = jsestate->jsexpr->result_coercion; - JsonExpr *jexpr = jsestate->jsexpr; - Jsonb *jb = *isNull ? NULL : DatumGetJsonbP(res); - - if ((coercion && coercion->via_io) || - (jexpr->omit_quotes && !*isNull && - JB_ROOT_IS_SCALAR(jb))) - { - /* strip quotes and call typinput function */ - char *str = *isNull ? NULL : JsonbUnquote(jb); - - return InputFunctionCall(&jsestate->input.func, str, - jsestate->input.typioparam, - jexpr->returning->typmod); - } - else if (coercion && coercion->via_populate) - return json_populate_type(res, JSONBOID, - jexpr->returning->typid, - jexpr->returning->typmod, - &jsestate->cache, - econtext->ecxt_per_query_memory, - isNull); - } - - if (jsestate->result_expr) - { - jsestate->res_expr->value = res; - jsestate->res_expr->isnull = *isNull; - - res = ExecEvalExpr(jsestate->result_expr, econtext, isNull); - } - - return res; -} - /* * Evaluate a JSON path variable caching computed value. */ @@ -4844,84 +4754,394 @@ EvalJsonPathVar(void *cxt, char *varName, int varNameLen, } /* - * Prepare SQL/JSON item coercion to the output type. Returned a datum of the - * corresponding SQL type and a pointer to the coercion state. + * Check whether we need to override default coercion in + * JSON_QUERY(OMIT QUOTES) case. */ -Datum -ExecPrepareJsonItemCoercion(JsonbValue *item, - JsonReturning *returning, - struct JsonCoercionsState *coercions, - struct JsonCoercionState **pcoercion) +static bool +ExecJsonQueryNeedsIOCoercion(JsonExpr *jsexpr, Datum res, bool isnull) { - struct JsonCoercionState *coercion; - Datum res; - JsonbValue buf; + if (jsexpr->omit_quotes && !isnull) + { + Jsonb *jb = DatumGetJsonbP(res); + JsonbValue jbv; + + /* Coerce string items via I/O in OMIT QUOTES case */ + return JsonbExtractScalar(&jb->root, &jbv) && + jbv.type == jbvString; + } + + return false; +} + +/* Coerce C string to text, varchar(N), or bpchar(N) */ +static Datum +ExecJsonStringCoercion(const char *str, int32 len, Oid typid, int32 typmod) +{ + if (typid == TEXTOID || typid == JSONOID || typmod <= VARHDRSZ) + return PointerGetDatum(cstring_to_text_with_len(str, len)); + else + { + char *txt; + int32 size; + + len = VARHDRSZ + len; - if (item->type == jbvBinary && - JsonContainerIsScalar(item->val.binary.data)) + if (typid == BPCHAROID) + size = typmod; + else + size = Min(len, typmod); + + txt = palloc(size); + SET_VARSIZE(txt, size); + + memcpy(VARDATA(txt), str, Min(size, len) - VARHDRSZ); + + if (len < size) + memset(txt + len, ' ', size - len); + + return PointerGetDatum(txt); + } +} + +/* Coerce SQL/JSON item to text */ +static Datum +ExecJsonCoercionToText(PGFunction outfunc, Datum value, Oid typid, int32 typmod) +{ + char *str = DatumGetCString(DirectFunctionCall1(outfunc, value)); + + return ExecJsonStringCoercion(str, strlen(str), typid, typmod); +} + +/* Coerce datetime SQL/JSON item to the output typid */ +static Datum +ExecJsonDatetimeCoercion(Datum val, Oid val_typid, Oid typid, int32 typmod, + bool *isnull, bool *error) +{ + if (val_typid == typid) + return val; + + switch (val_typid) { - bool res PG_USED_FOR_ASSERTS_ONLY; + case DATEOID: + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(date_out, val, typid, typmod); + else if (typid == DATEOID) + return val; + else if (typid == TIMESTAMPOID) + { + int overflow = 0; + Timestamp ts = + date2timestamp_opt_overflow(DatumGetDateADT(val), + error ? &overflow : NULL); + + if (overflow) + { + *error = true; + return (Datum) 0; + } - res = JsonbExtractScalar(item->val.binary.data, &buf); - item = &buf; - Assert(res); + return TimestampGetDatum(ts); + } + else if (typid == TIMESTAMPTZOID) + { + int overflow = 0; + TimestampTz res = + date2timestamptz_opt_overflow(DatumGetDateADT(val), + error ? &overflow : NULL); + + if (overflow) + { + *error = true; + return (Datum) 0; + } + + return TimestampTzGetDatum(res); + } + else + break; /* No cast */ + + case TIMEOID: + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(time_out, val, typid, typmod); + else if (typid == TIMEOID) + return val; + else if (typid == TIMETZOID) + return DirectFunctionCall1(time_timetz, val); + else + break; /* No cast */ + + case TIMETZOID: + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(timetz_out, val, typid, typmod); + else if (typid == TIMETZOID) + return val; + else if (typid == TIMEOID) + return DirectFunctionCall1(timetz_time, val); + else + break; + + case TIMESTAMPOID: + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(timestamp_out, val, typid, typmod); + else if (typid == TIMESTAMPOID) + return val; + else if (typid == DATEOID) + return timestamp_date_opt_error(DatumGetTimestamp(val), error); + else if (typid == TIMEOID) + { + TimeADT time = + timestamp_time_opt_error(DatumGetTimestamp(val), + isnull, error); + + if ((error && *error) || *isnull) + return (Datum) 0; + else + return TimeADTGetDatum(time); + } + else if (typid == TIMESTAMPTZOID) + { + int overflow = 0; + TimestampTz tstz = + timestamp2timestamptz_opt_overflow(DatumGetTimestamp(val), + error ? &overflow : NULL); + + if (overflow) + { + *error = true; + return (Datum) 0; + } + + return TimestampTzGetDatum(tstz); + } + else + break; /* No cast */ + + case TIMESTAMPTZOID: + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(timestamptz_out, val, typid, typmod); + else if (typid == TIMESTAMPTZOID) + return val; + else if (typid == DATEOID) + return timestamptz_date_opt_error(DatumGetTimestampTz(val), error); + else if (typid == TIMEOID) + { + TimeADT time = + timestamptz_time_opt_error(DatumGetTimestampTz(val), + isnull, error); + + if ((error && *error) || *isnull) + return (Datum) 0; + else + return TimeADTGetDatum(time); + } + else if (typid == TIMETZOID) + { + TimeTzADT *result = + timestamptz_timetz_opt_error(DatumGetTimestampTz(val), error); + + if ((error && *error) || !result) + { + *isnull = true; + return (Datum) 0; + } + + return TimeTzADTPGetDatum(result); + } + else if (typid == TIMESTAMPOID) + { + Timestamp ts = + timestamptz2timestamp_opt_error(DatumGetTimestampTz(val), + error); + + if (error && *error) + return (Datum) 0; + + return TimestampGetDatum(ts); + } + else + break; /* No cast */ + + default: + elog(ERROR, "unexpected jsonb datetime type oid %u", val_typid); + break; } - /* get coercion state reference and datum of the corresponding SQL type */ + if (!error) + ereport(ERROR, + (errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE), + errmsg("SQL/JSON item cannot be cast to target type"))); + + *error = true; + *isnull = true; + + return (Datum) 0; +} + +/* Coerce boolean SQL/JSON item or JSON_EXISTS result to the output type */ +static bool +ExecJsonBoolCoercion(bool val, Oid typid, int32 typmod, Datum *res) +{ + if (typid == BOOLOID) + *res = BoolGetDatum(val); + else if (typid == INT4OID) + /* We only have cast bool::int4 in the catalog. */ + *res = Int32GetDatum(val ? 1 : 0); + else if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + /* + * bool::text returns 'true' / 'false', + * boolout() returns 't' / 'f'. + */ + *res = ExecJsonStringCoercion(val ? "true" : "false", val ? 4 : 5, + typid, typmod); + else + return false; + + return true; +} + +static Datum +JsonbPGetTextDatum(Jsonb *jb) +{ + char *str = JsonbToCString(NULL, &jb->root, VARSIZE(jb)); + Datum res = CStringGetTextDatum(str); + + pfree(str); + return res; +} + +/* Coerce SQL/JSON item to the output typid */ +static Datum +ExecJsonValueCoercion(JsonbValue *item, Oid typid, int32 typmod, + bool *isnull, bool *error) +{ + *isnull = false; + + /* Special case for json and jsonb output types */ + if (typid == JSONBOID) + return JsonbPGetDatum(JsonbValueToJsonb(item)); + + if (typid == JSONOID) + return JsonbPGetTextDatum(JsonbValueToJsonb(item)); + + /* + * Coercion method and set of supported output types are determined + * by the item type. + */ switch (item->type) { case jbvNull: - coercion = &coercions->null; - res = (Datum) 0; - break; + Assert(0); /* must be handled by the caller */ + *isnull = true; + return (Datum) 0; case jbvString: - coercion = &coercions->string; - res = PointerGetDatum(cstring_to_text_with_len(item->val.string.val, + if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + { + return ExecJsonStringCoercion(item->val.string.val, + item->val.string.len, + typid, typmod); + } + else if (typid == INT2OID || typid == INT4OID || typid == INT8OID) + { + char *str = pnstrdup(item->val.string.val, + item->val.string.len); + + if (error) + { + int64 val = pg_strtoint64_opt_error(str, error); + + if (*error) + return (Datum) 0; + + if (typid == INT2OID) + { + if (val <= PG_INT16_MAX || val >= PG_INT16_MIN) + return Int16GetDatum((int16) val); + } + else if (typid == INT4OID) + { + if (val <= PG_INT32_MAX || val >= PG_INT32_MIN) + return Int32GetDatum((int32) val); + } + else + return Int64GetDatum(val); + + *error = true; + return (Datum) 0; + } + else if (typid == INT2OID) + return Int16GetDatum(pg_strtoint16(str)); + else if (typid == INT4OID) + return Int32GetDatum(pg_strtoint32(str)); + else + return Int64GetDatum(pg_strtoint64(str)); + } + else if (typid == BOOLOID) + { + return BoolGetDatum(boolin_opt_error(item->val.string.val, + item->val.string.len, + error)); + } + else + { + Datum str = CStringGetDatum(pnstrdup(item->val.string.val, item->val.string.len)); - break; + + if (typid == NUMERICOID) + return DirectFunctionCall3(numeric_in, str, + ObjectIdGetDatum(0), + Int32GetDatum(typmod)); /* FIXME errors */ + else if (typid == DATEOID) + return DirectFunctionCall1(date_in, str); /* FIXME errors */ + else if (typid == TIMEOID) + return DirectFunctionCall1(time_in, str); /* FIXME errors */ + else if (typid == TIMETZOID) + return DirectFunctionCall1(timetz_in, str); /* FIXME errors */ + else if (typid == TIMESTAMPOID) + return DirectFunctionCall1(timestamp_in, str); /* FIXME errors */ + else if (typid == TIMESTAMPTZOID) + return DirectFunctionCall1(timestamptz_in, str); /* FIXME errors */ + else + break; /* No cast */ + } case jbvNumeric: - coercion = &coercions->numeric; - res = NumericGetDatum(item->val.numeric); - break; + { + Numeric num = item->val.numeric; + + if (typid == NUMERICOID) + return NumericGetDatum(num); + else if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID) + return ExecJsonCoercionToText(numeric_out, NumericGetDatum(num), typid, typmod); + else if (typid == INT2OID) + return Int16GetDatum(numeric_int2_opt_error(num, error)); + else if (typid == INT4OID) + return Int32GetDatum(numeric_int4_opt_error(num, error)); + else if (typid == INT8OID) + return Int64GetDatum(numeric_int8_opt_error(num, error)); + else + break; /* No cast */ + } case jbvBool: - coercion = &coercions->boolean; - res = BoolGetDatum(item->val.boolean); - break; - - case jbvDatetime: - res = item->val.datetime.value; - switch (item->val.datetime.typid) { - case DATEOID: - coercion = &coercions->date; - break; - case TIMEOID: - coercion = &coercions->time; - break; - case TIMETZOID: - coercion = &coercions->timetz; - break; - case TIMESTAMPOID: - coercion = &coercions->timestamp; - break; - case TIMESTAMPTZOID: - coercion = &coercions->timestamptz; - break; - default: - elog(ERROR, "unexpected jsonb datetime type oid %u", - item->val.datetime.typid); - return (Datum) 0; + Datum res; + + if (ExecJsonBoolCoercion(item->val.boolean, typid, typmod, &res)) + return res; + + break; /* No cast */ } - break; + + case jbvDatetime: + return ExecJsonDatetimeCoercion(item->val.datetime.value, + item->val.datetime.typid, + typid, typmod, isnull, error); case jbvArray: case jbvObject: case jbvBinary: - coercion = &coercions->composite; - res = JsonbPGetDatum(JsonbValueToJsonb(item)); + Assert(0); /* non-scalars must be rejected by JsonPathValue() */ break; default: @@ -4929,99 +5149,98 @@ ExecPrepareJsonItemCoercion(JsonbValue *item, return (Datum) 0; } - *pcoercion = coercion; + if (!error) + ereport(ERROR, + (errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE), + errmsg("SQL/JSON item cannot be cast to target type"))); - return res; -} + *error = true; + *isnull = true; -typedef Datum (*JsonFunc) (ExprEvalStep *op, ExprContext *econtext, - Datum item, bool *resnull, void *p, bool *error); + return (Datum) 0; +} +/* + * Evaluate a JSON error/empty behavior and coerce result to the output + * type. + */ static Datum -ExecEvalJsonExprSubtrans(JsonFunc func, ExprEvalStep *op, - ExprContext *econtext, - Datum res, bool *resnull, - void *p, bool *error, bool subtrans) +ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior, + ExprState *default_estate, + Oid ret_typid, int32 ret_typmod, bool *is_null) { - MemoryContext oldcontext; - ResourceOwner oldowner; - - if (!subtrans) - /* No need to use subtransactions. */ - return func(op, econtext, res, resnull, p, error); + *is_null = false; - /* - * We should catch exceptions of category ERRCODE_DATA_EXCEPTION and - * execute the corresponding ON ERROR behavior then. - */ - oldcontext = CurrentMemoryContext; - oldowner = CurrentResourceOwner; + switch (behavior->btype) + { + case JSON_BEHAVIOR_EMPTY_ARRAY: + if (ret_typid == JSONBOID) + return JsonbPGetDatum(JsonbMakeEmptyArray()); + else + { + Assert(ret_typid == JSONOID || ret_typid == TEXTOID || + ret_typid == VARCHAROID || ret_typid == BPCHAROID); - Assert(error); + return ExecJsonStringCoercion("[]", 2, ret_typid, ret_typmod); + } - BeginInternalSubTransaction(NULL); - /* Want to execute expressions inside function's memory context */ - MemoryContextSwitchTo(oldcontext); + case JSON_BEHAVIOR_EMPTY_OBJECT: + if (ret_typid == JSONBOID) + return JsonbPGetDatum(JsonbMakeEmptyObject()); + else + { + Assert(ret_typid == JSONOID || ret_typid == TEXTOID || + ret_typid == VARCHAROID || ret_typid == BPCHAROID); - PG_TRY(); - { - res = func(op, econtext, res, resnull, p, error); + return ExecJsonStringCoercion("{}", 2, ret_typid, ret_typmod); + } - /* Commit the inner transaction, return to outer xact context */ - ReleaseCurrentSubTransaction(); - MemoryContextSwitchTo(oldcontext); - CurrentResourceOwner = oldowner; - } - PG_CATCH(); - { - ErrorData *edata; - int ecategory; + case JSON_BEHAVIOR_TRUE: + case JSON_BEHAVIOR_FALSE: + { + Datum res; + bool ok = + ExecJsonBoolCoercion(behavior->btype == JSON_BEHAVIOR_TRUE, + ret_typid, ret_typmod, &res); - /* Save error info in oldcontext */ - MemoryContextSwitchTo(oldcontext); - edata = CopyErrorData(); - FlushErrorState(); + Assert(ok); /* returning type must be check in parser */ - /* Abort the inner transaction */ - RollbackAndReleaseCurrentSubTransaction(); - MemoryContextSwitchTo(oldcontext); - CurrentResourceOwner = oldowner; + return res; + } - ecategory = ERRCODE_TO_CATEGORY(edata->sqlerrcode); + case JSON_BEHAVIOR_NULL: + case JSON_BEHAVIOR_UNKNOWN: + case JSON_BEHAVIOR_EMPTY: + *is_null = true; + return (Datum) 0; - if (ecategory != ERRCODE_DATA_EXCEPTION && /* jsonpath and other data - * errors */ - ecategory != ERRCODE_INTEGRITY_CONSTRAINT_VIOLATION) /* domain errors */ - ReThrowError(edata); + case JSON_BEHAVIOR_DEFAULT: + /* + * Execute DEFAULT expression. + * Coercion is not needed here, because expression is + * already coerced to the target type by the parser. + */ + return ExecEvalExpr(default_estate, econtext, is_null); - res = (Datum) 0; - *error = true; + default: + elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype); + return (Datum) 0; } - PG_END_TRY(); - - return res; } - -typedef struct -{ - JsonPath *path; - bool *error; - bool coercionInSubtrans; -} ExecEvalJsonExprContext; - static Datum -ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext, - Datum item, bool *resnull, void *pcxt, - bool *error) +ExecEvalJsonExprInternal(ExprState *state, + JsonExprState *jsestate, ExprContext *econtext, + JsonPath *path, Datum item, bool *resnull, + bool *error) { - ExecEvalJsonExprContext *cxt = pcxt; - JsonPath *path = cxt->path; - JsonExprState *jsestate = op->d.jsonexpr.jsestate; JsonExpr *jexpr = jsestate->jsexpr; - ExprState *estate = NULL; bool empty = false; Datum res = (Datum) 0; + Oid ret_typid = jexpr->returning->typid; + int32 ret_typmod = jexpr->returning->typmod; + + *resnull = true; switch (jexpr->op) { @@ -5029,70 +5248,94 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext, res = JsonPathQuery(item, path, jexpr->wrapper, &empty, error, jsestate->args); if (error && *error) - { - *resnull = true; return (Datum) 0; - } + + if (empty) + break; + *resnull = !DatumGetPointer(res); - break; + + /* Override default coercion in OMIT QUOTES case */ + if (ExecJsonQueryNeedsIOCoercion(jexpr, res, *resnull)) + { + char *str = JsonbUnquote(DatumGetJsonbP(res)); + + if (ret_typid == JSONBOID) + { + res = jsonb_from_cstring(str, strlen(str), false, error); + + if (error && *error) + return (Datum) 0; + + return res; + } + else if (ret_typid == JSONOID) + { + text *json = cstring_to_text(str); + JsonLexContext *lex; + JsonParseErrorType result; + + /* validate it */ + lex = makeJsonLexContext(json, false); + result = pg_parse_json(lex, &nullSemAction); + + if (result != JSON_SUCCESS) + { + if (error) + { + *error = true; + return (Datum) 0; + } + + json_ereport_error(result, lex); + } + + return PointerGetDatum(json); + } + else if (ret_typid == TEXTOID || + ret_typid == VARCHAROID || + ret_typid == BPCHAROID) + return ExecJsonStringCoercion(str, strlen(str), ret_typid, ret_typmod); + } + else if (ret_typid == JSONBOID) + return res; + else if (ret_typid == JSONOID || ret_typid == TEXTOID) + return JsonbPGetTextDatum(DatumGetJsonbP(res)); + else if (ret_typid == VARCHAROID || ret_typid == BPCHAROID) + { + Jsonb *jb = DatumGetJsonbP(res); + char *str = JsonbToCString(NULL, &jb->root, VARSIZE(jb)); + + return ExecJsonStringCoercion(str, strlen(str), ret_typid, ret_typmod); + } + + Assert(0); /* unsupported output type */ + *error = *resnull = true; + return (Datum) 0; case JSON_VALUE_OP: { - struct JsonCoercionState *jcstate; JsonbValue *jbv = JsonPathValue(item, path, &empty, error, jsestate->args); if (error && *error) return (Datum) 0; - if (!jbv) /* NULL or empty */ + if (empty) break; - Assert(!empty); + if (!jbv) + return (Datum) 0; /* NULL */ - *resnull = false; + Assert(jbv->type != jbvNull); - /* coerce scalar item to the output type */ - if (jexpr->returning->typid == JSONOID || - jexpr->returning->typid == JSONBOID) - { - /* Use result coercion from json[b] to the output type */ - res = JsonbPGetDatum(JsonbValueToJsonb(jbv)); - break; - } + res = ExecJsonValueCoercion(jbv, ret_typid, ret_typmod, + resnull, error); - /* Use coercion from SQL/JSON item type to the output type */ - res = ExecPrepareJsonItemCoercion(jbv, - jsestate->jsexpr->returning, - &jsestate->coercions, - &jcstate); - - if (jcstate->coercion && - (jcstate->coercion->via_io || - jcstate->coercion->via_populate)) - { - if (error) - { - *error = true; - return (Datum) 0; - } - - /* - * Coercion via I/O means here that the cast to the target - * type simply does not exist. - */ - ereport(ERROR, - (errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE), - errmsg("SQL/JSON item cannot be cast to target type"))); - } - else if (!jcstate->estate) - return res; /* no coercion */ + if (error && *error) + return (Datum) 0; - /* coerce using specific expression */ - estate = jcstate->estate; - jsestate->coercion_expr->value = res; - jsestate->coercion_expr->isnull = *resnull; - break; + return res; } case JSON_EXISTS_OP: @@ -5101,113 +5344,81 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext, jsestate->args, error); - *resnull = error && *error; - res = BoolGetDatum(exists); - - if (!jsestate->result_expr) - return res; + if (!error || !*error) + { + /* Should succeed, output type is checked by parser */ + (void) ExecJsonBoolCoercion(exists, ret_typid, ret_typmod, &res); + *resnull = false; + } - /* coerce using result expression */ - estate = jsestate->result_expr; - jsestate->res_expr->value = res; - jsestate->res_expr->isnull = *resnull; - break; + return res; } - case JSON_TABLE_OP: - *resnull = false; - return item; - default: elog(ERROR, "unrecognized SQL/JSON expression op %d", jexpr->op); return (Datum) 0; } - if (empty) - { - Assert(jexpr->on_empty); /* it is not JSON_EXISTS */ + Assert(empty); + Assert(jexpr->on_empty); /* it is not JSON_EXISTS */ - if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR) + if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR) + { + if (error) { - if (error) - { - *error = true; - return (Datum) 0; - } - - ereport(ERROR, - (errcode(ERRCODE_NO_SQL_JSON_ITEM), - errmsg("no SQL/JSON item"))); + *error = true; + return (Datum) 0; } - if (jexpr->on_empty->btype == JSON_BEHAVIOR_DEFAULT) - - /* - * Execute DEFAULT expression as a coercion expression, because - * its result is already coerced to the target type. - */ - estate = jsestate->default_on_empty; - else - /* Execute ON EMPTY behavior */ - res = ExecEvalJsonBehavior(econtext, jexpr->on_empty, - jsestate->default_on_empty, - resnull); + ereport(ERROR, + (errcode(ERRCODE_NO_SQL_JSON_ITEM), + errmsg("no SQL/JSON item"))); } - return ExecEvalJsonExprSubtrans(ExecEvalJsonExprCoercion, op, econtext, - res, resnull, estate, error, - cxt->coercionInSubtrans); + /* Execute ON EMPTY behavior */ + return ExecEvalJsonBehavior(econtext, jexpr->on_empty, + jsestate->default_on_empty, + ret_typid, ret_typmod, resnull); } bool -ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr, - struct JsonCoercionsState *coercions) +ExecEvalExprCanThrowErrors(Node *expr) { - if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR) + if (!expr) return false; - if (jsexpr->op == JSON_EXISTS_OP && !jsexpr->result_coercion) + if (IsA(expr, Const)) return false; - if (!coercions) - return true; - - return false; + /* TODO consider more cases */ + return true; } /* ---------------------------------------------------------------- - * ExecEvalJson + * ExecEvalJsonExpr * ---------------------------------------------------------------- */ void -ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext) +ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext) { - ExecEvalJsonExprContext cxt; JsonExprState *jsestate = op->d.jsonexpr.jsestate; JsonExpr *jexpr = jsestate->jsexpr; Datum item; - Datum res = (Datum) 0; + Datum res; JsonPath *path; ListCell *lc; bool error = false; - bool needSubtrans; bool throwErrors = jexpr->on_error->btype == JSON_BEHAVIOR_ERROR; - *op->resnull = true; /* until we get a result */ - *op->resvalue = (Datum) 0; - - if (jsestate->formatted_expr->isnull || jsestate->pathspec->isnull) + if (jsestate->formatted_expr.isnull || jsestate->pathspec.isnull) { - /* execute domain checks for NULLs */ - (void) ExecEvalJsonExprCoercion(op, econtext, res, op->resnull, - NULL, NULL); - - Assert(*op->resnull); + *op->resnull = true; + *op->resvalue = (Datum) 0; return; } - item = jsestate->formatted_expr->value; - path = DatumGetJsonPathP(jsestate->pathspec->value); + item = jsestate->formatted_expr.value; + path = DatumGetJsonPathP(jsestate->pathspec.value); /* reset JSON path variable contexts */ foreach(lc, jsestate->args) @@ -5218,29 +5429,20 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext) var->evaluated = false; } - needSubtrans = ExecEvalJsonNeedsSubTransaction(jexpr, &jsestate->coercions); - - cxt.path = path; - cxt.error = throwErrors ? NULL : &error; - cxt.coercionInSubtrans = !needSubtrans && !throwErrors; - Assert(!needSubtrans || cxt.error); - - res = ExecEvalJsonExprSubtrans(ExecEvalJsonExpr, op, econtext, item, - op->resnull, &cxt, cxt.error, - needSubtrans); + res = ExecEvalJsonExprInternal(state, jsestate, econtext, + path, item, op->resnull, + throwErrors ? NULL : &error); if (error) { + Assert(!throwErrors); + /* Execute ON ERROR behavior */ res = ExecEvalJsonBehavior(econtext, jexpr->on_error, jsestate->default_on_error, + jexpr->returning->typid, + jexpr->returning->typmod, op->resnull); - - /* result is already coerced in DEFAULT behavior case */ - if (jexpr->on_error->btype != JSON_BEHAVIOR_DEFAULT) - res = ExecEvalJsonExprCoercion(op, econtext, res, - op->resnull, - NULL, NULL); } *op->resvalue = res; diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c index bd3965143da..fd72630f5e6 100644 --- a/src/backend/jit/llvm/llvmjit_expr.c +++ b/src/backend/jit/llvm/llvmjit_expr.c @@ -2408,7 +2408,7 @@ llvm_compile_expr(ExprState *state) break; case EEOP_JSONEXPR: - build_EvalXFunc(b, mod, "ExecEvalJson", + build_EvalXFunc(b, mod, "ExecEvalJsonExpr", v_state, op, v_econtext); LLVMBuildBr(b, opblocks[opno + 1]); break; diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c index 373471ad27f..37fe64654b6 100644 --- a/src/backend/jit/llvm/llvmjit_types.c +++ b/src/backend/jit/llvm/llvmjit_types.c @@ -135,7 +135,7 @@ void *referenced_functions[] = ExecEvalXmlExpr, ExecEvalJsonConstructor, ExecEvalJsonIsPredicate, - ExecEvalJson, + ExecEvalJsonExpr, MakeExpandedObjectReadOnlyInternal, slot_getmissingattrs, slot_getsomeattrs_int, diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index c334daae392..e0240beeeab 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -266,9 +266,6 @@ exprType(const Node *expr) case T_JsonExpr: type = ((const JsonExpr *) expr)->returning->typid; break; - case T_JsonCoercion: - type = exprType(((const JsonCoercion *) expr)->expr); - break; default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr)); type = InvalidOid; /* keep compiler quiet */ @@ -507,8 +504,6 @@ exprTypmod(const Node *expr) return ((const JsonConstructorExpr *) expr)->returning->typmod; case T_JsonExpr: return ((JsonExpr *) expr)->returning->typmod; - case T_JsonCoercion: - return exprTypmod(((const JsonCoercion *) expr)->expr); default: break; } @@ -1010,14 +1005,12 @@ exprCollation(const Node *expr) case T_JsonExpr: { JsonExpr *jexpr = (JsonExpr *) expr; - JsonCoercion *coercion = jexpr->result_coercion; - if (!coercion) - coll = InvalidOid; - else if (coercion->expr) - coll = exprCollation(coercion->expr); - else if (coercion->via_io || coercion->via_populate) - coll = coercion->collation; + if (jexpr->returning->typid == TEXTOID || + jexpr->returning->typid == CHAROID || + jexpr->returning->typid == VARCHAROID || + jexpr->returning->typid == BPCHAROID) + coll = jexpr->collation; else coll = InvalidOid; } @@ -1255,14 +1248,12 @@ exprSetCollation(Node *expr, Oid collation) case T_JsonExpr: { JsonExpr *jexpr = (JsonExpr *) expr; - JsonCoercion *coercion = jexpr->result_coercion; - if (!coercion) - Assert(!OidIsValid(collation)); - else if (coercion->expr) - exprSetCollation(coercion->expr, collation); - else if (coercion->via_io || coercion->via_populate) - coercion->collation = collation; + if (jexpr->returning->typid == TEXTOID || + jexpr->returning->typid == CHAROID || + jexpr->returning->typid == VARCHAROID || + jexpr->returning->typid == BPCHAROID) + jexpr->collation = collation; else Assert(!OidIsValid(collation)); } @@ -2507,8 +2498,6 @@ expression_tree_walker(Node *node, if (walker(jexpr->formatted_expr, context)) return true; - if (walker(jexpr->result_coercion, context)) - return true; if (walker(jexpr->passing_values, context)) return true; /* we assume walker doesn't care about passing_names */ @@ -2517,36 +2506,6 @@ expression_tree_walker(Node *node, return true; if (walker(jexpr->on_error->default_expr, context)) return true; - if (walker(jexpr->coercions, context)) - return true; - } - break; - case T_JsonCoercion: - return walker(((JsonCoercion *) node)->expr, context); - case T_JsonItemCoercions: - { - JsonItemCoercions *coercions = (JsonItemCoercions *) node; - - if (walker(coercions->null, context)) - return true; - if (walker(coercions->string, context)) - return true; - if (walker(coercions->numeric, context)) - return true; - if (walker(coercions->boolean, context)) - return true; - if (walker(coercions->date, context)) - return true; - if (walker(coercions->time, context)) - return true; - if (walker(coercions->timetz, context)) - return true; - if (walker(coercions->timestamp, context)) - return true; - if (walker(coercions->timestamptz, context)) - return true; - if (walker(coercions->composite, context)) - return true; } break; default: @@ -3576,7 +3535,6 @@ expression_tree_mutator(Node *node, FLATCOPY(newnode, jexpr, JsonExpr); MUTATE(newnode->path_spec, jexpr->path_spec, Node *); MUTATE(newnode->formatted_expr, jexpr->formatted_expr, Node *); - MUTATE(newnode->result_coercion, jexpr->result_coercion, JsonCoercion *); MUTATE(newnode->passing_values, jexpr->passing_values, List *); /* assume mutator does not care about passing_names */ if (newnode->on_empty) @@ -3587,35 +3545,6 @@ expression_tree_mutator(Node *node, return (Node *) newnode; } break; - case T_JsonCoercion: - { - JsonCoercion *coercion = (JsonCoercion *) node; - JsonCoercion *newnode; - - FLATCOPY(newnode, coercion, JsonCoercion); - MUTATE(newnode->expr, coercion->expr, Node *); - return (Node *) newnode; - } - break; - case T_JsonItemCoercions: - { - JsonItemCoercions *coercions = (JsonItemCoercions *) node; - JsonItemCoercions *newnode; - - FLATCOPY(newnode, coercions, JsonItemCoercions); - MUTATE(newnode->null, coercions->null, JsonCoercion *); - MUTATE(newnode->string, coercions->string, JsonCoercion *); - MUTATE(newnode->numeric, coercions->numeric, JsonCoercion *); - MUTATE(newnode->boolean, coercions->boolean, JsonCoercion *); - MUTATE(newnode->date, coercions->date, JsonCoercion *); - MUTATE(newnode->time, coercions->time, JsonCoercion *); - MUTATE(newnode->timetz, coercions->timetz, JsonCoercion *); - MUTATE(newnode->timestamp, coercions->timestamp, JsonCoercion *); - MUTATE(newnode->timestamptz, coercions->timestamptz, JsonCoercion *); - MUTATE(newnode->composite, coercions->composite, JsonCoercion *); - return (Node *) newnode; - } - break; default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node)); diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 533df86ff77..3c14ddfba2f 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -896,18 +896,6 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context) context, 0); } - /* JsonExpr is parallel-unsafe if subtransactions can be used. */ - else if (IsA(node, JsonExpr)) - { - JsonExpr *jsexpr = (JsonExpr *) node; - - if (ExecEvalJsonNeedsSubTransaction(jsexpr, NULL)) - { - context->max_hazard = PROPARALLEL_UNSAFE; - return true; - } - } - /* Recurse to check arguments */ return expression_tree_walker(node, max_parallel_hazard_walker, diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index fabb5f72076..a6e5fe1eb93 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4108,7 +4108,6 @@ transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func) /* format is determined by context item type */ format = exprType(jsexpr->formatted_expr) == JSONBOID ? JS_FORMAT_JSONB : JS_FORMAT_JSON; - jsexpr->result_coercion = NULL; jsexpr->omit_quotes = false; jsexpr->format = func->common->expr->format; @@ -4169,40 +4168,6 @@ assignDefaultJsonReturningType(Node *context_item, JsonFormat *context_format, ret->typmod = -1; } -/* - * Try to coerce expression to the output type or - * use json_populate_type() for composite, array and domain types or - * use coercion via I/O. - */ -static JsonCoercion * -coerceJsonExpr(ParseState *pstate, Node *expr, const JsonReturning *returning) -{ - char typtype; - JsonCoercion *coercion = makeNode(JsonCoercion); - - coercion->expr = coerceJsonFuncExpr(pstate, expr, returning, false); - - if (coercion->expr) - { - if (coercion->expr == expr) - coercion->expr = NULL; - - return coercion; - } - - typtype = get_typtype(returning->typid); - - if (returning->typid == RECORDOID || - typtype == TYPTYPE_COMPOSITE || - typtype == TYPTYPE_DOMAIN || - type_is_array(returning->typid)) - coercion->via_populate = true; - else - coercion->via_io = true; - - return coercion; -} - /* * Transform a JSON output clause of JSON_VALUE and JSON_QUERY. */ @@ -4210,8 +4175,6 @@ static void transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, JsonExpr *jsexpr) { - Node *expr = jsexpr->formatted_expr; - jsexpr->returning = transformJsonOutput(pstate, func->output, false); /* JSON_VALUE returns text by default */ @@ -4225,14 +4188,41 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, { JsonReturning ret; - if (func->op == JSON_VALUE_OP && - jsexpr->returning->typid != JSONOID && - jsexpr->returning->typid != JSONBOID) + if (func->op == JSON_VALUE_OP) { - /* Forced coercion via I/O for JSON_VALUE for non-JSON types */ - jsexpr->result_coercion = makeNode(JsonCoercion); - jsexpr->result_coercion->expr = NULL; - jsexpr->result_coercion->via_io = true; + /* + * Only a limited list of output types is supported in + * JSON_VALUE() now. + */ + switch (jsexpr->returning->typid) + { + case JSONBOID: + case JSONOID: + case TEXTOID: + case VARCHAROID: + case BPCHAROID: + case BOOLOID: + case NUMERICOID: + case INT2OID: + case INT4OID: + case INT8OID: + /* case FLOAT4OID: FIXME */ + /* case FLOAT8OID: FIXME */ + case DATEOID: + case TIMEOID: + case TIMETZOID: + case TIMESTAMPOID: + case TIMESTAMPTZOID: + break; /* Ok */ + + default: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("returning type %s is not supported in JSON_VALUE()", + format_type_be(jsexpr->returning->typid)), + parser_coercion_errposition(pstate, func->location, (Node *) jsexpr))); + } + return; } @@ -4241,13 +4231,22 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func, if (ret.typid != jsexpr->returning->typid || ret.typmod != jsexpr->returning->typmod) { - Node *placeholder = makeCaseTestExpr(expr); - - Assert(((CaseTestExpr *) placeholder)->typeId == ret.typid); - Assert(((CaseTestExpr *) placeholder)->typeMod == ret.typmod); + switch (jsexpr->returning->typid) + { + case JSONBOID: + case JSONOID: + case TEXTOID: + case VARCHAROID: + case BPCHAROID: + break; - jsexpr->result_coercion = coerceJsonExpr(pstate, placeholder, - jsexpr->returning); + default: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("returning type %s is not supported in JSON_QUERY()", + format_type_be(jsexpr->returning->typid)), + parser_coercion_errposition(pstate, func->location, (Node *) jsexpr))); + } } } else @@ -4293,62 +4292,6 @@ coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, Node *defexpr) return defexpr; } -/* - * Initialize SQL/JSON item coercion from the SQL type "typid" to the target - * "returning" type. - */ -static JsonCoercion * -initJsonItemCoercion(ParseState *pstate, Oid typid, - const JsonReturning *returning) -{ - Node *expr; - - if (typid == UNKNOWNOID) - { - expr = (Node *) makeNullConst(UNKNOWNOID, -1, InvalidOid); - } - else - { - CaseTestExpr *placeholder = makeNode(CaseTestExpr); - - placeholder->typeId = typid; - placeholder->typeMod = -1; - placeholder->collation = InvalidOid; - - expr = (Node *) placeholder; - } - - return coerceJsonExpr(pstate, expr, returning); -} - -static void -initJsonItemCoercions(ParseState *pstate, JsonItemCoercions *coercions, - const JsonReturning *returning, Oid contextItemTypeId) -{ - struct - { - JsonCoercion **coercion; - Oid typid; - } *p, - coercionTypids[] = - { - {&coercions->null, UNKNOWNOID}, - {&coercions->string, TEXTOID}, - {&coercions->numeric, NUMERICOID}, - {&coercions->boolean, BOOLOID}, - {&coercions->date, DATEOID}, - {&coercions->time, TIMEOID}, - {&coercions->timetz, TIMETZOID}, - {&coercions->timestamp, TIMESTAMPOID}, - {&coercions->timestamptz, TIMESTAMPTZOID}, - {&coercions->composite, contextItemTypeId}, - {NULL, InvalidOid} - }; - - for (p = coercionTypids; p->coercion; p++) - *p->coercion = initJsonItemCoercion(pstate, p->typid, returning); -} - /* * Transform JSON_VALUE, JSON_QUERY, JSON_EXISTS functions into a JsonExpr node. */ @@ -4377,10 +4320,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) coerceDefaultJsonExpr(pstate, jsexpr, jsexpr->on_error->default_expr); - jsexpr->coercions = makeNode(JsonItemCoercions); - initJsonItemCoercions(pstate, jsexpr->coercions, jsexpr->returning, - exprType(contextItemExpr)); - break; case JSON_QUERY_OP: @@ -4409,6 +4348,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) jsexpr->returning->format->format_type = JS_FORMAT_DEFAULT; jsexpr->returning->format->encoding = JS_ENC_DEFAULT; + /* Coerce intermediate boolean result to the output type if needed */ if (!OidIsValid(jsexpr->returning->typid)) { jsexpr->returning->typid = BOOLOID; @@ -4416,32 +4356,21 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) } else if (jsexpr->returning->typid != BOOLOID) { - CaseTestExpr *placeholder = makeNode(CaseTestExpr); - int location = exprLocation((Node *) jsexpr); - - placeholder->typeId = BOOLOID; - placeholder->typeMod = -1; - placeholder->collation = InvalidOid; - - jsexpr->result_coercion = makeNode(JsonCoercion); - jsexpr->result_coercion->expr = - coerce_to_target_type(pstate, (Node *) placeholder, BOOLOID, - jsexpr->returning->typid, - jsexpr->returning->typmod, - COERCION_EXPLICIT, - COERCE_IMPLICIT_CAST, - location); - - if (!jsexpr->result_coercion->expr) - ereport(ERROR, - (errcode(ERRCODE_CANNOT_COERCE), - errmsg("cannot cast type %s to %s", - format_type_be(BOOLOID), - format_type_be(jsexpr->returning->typid)), - parser_coercion_errposition(pstate, location, (Node *) jsexpr))); + switch (jsexpr->returning->typid) + { + case INT4OID: + case TEXTOID: + case VARCHAROID: + case BPCHAROID: + break; - if (jsexpr->result_coercion->expr == (Node *) placeholder) - jsexpr->result_coercion->expr = NULL; + default: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("returning type %s is not supported in JSON_EXISTS()", + format_type_be(jsexpr->returning->typid)), + parser_coercion_errposition(pstate, func->location, (Node *) jsexpr))); + } } break; @@ -4457,7 +4386,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) errmsg("JSON_TABLE() is not yet implemented for the json type"), errhint("Try casting the argument to jsonb"), parser_errposition(pstate, func->location))); - break; } diff --git a/src/backend/utils/adt/bool.c b/src/backend/utils/adt/bool.c index cd7335287f9..78557c99dc5 100644 --- a/src/backend/utils/adt/bool.c +++ b/src/backend/utils/adt/bool.c @@ -126,12 +126,10 @@ parse_bool_with_len(const char *value, size_t len, bool *result) * * In the switch statement, check the most-used possibilities first. */ -Datum -boolin(PG_FUNCTION_ARGS) +bool +boolin_opt_error(const char *in_str, size_t len, bool *error) { - const char *in_str = PG_GETARG_CSTRING(0); const char *str; - size_t len; bool result; /* @@ -141,20 +139,29 @@ boolin(PG_FUNCTION_ARGS) while (isspace((unsigned char) *str)) str++; - len = strlen(str); + len -= str - in_str; while (len > 0 && isspace((unsigned char) str[len - 1])) len--; if (parse_bool_with_len(str, len, &result)) - PG_RETURN_BOOL(result); + return result; + + if (!error) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("invalid input syntax for type %s: \"%s\"", + "boolean", in_str))); + + *error = true; + return false; +} - ereport(ERROR, - (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), - errmsg("invalid input syntax for type %s: \"%s\"", - "boolean", in_str))); +Datum +boolin(PG_FUNCTION_ARGS) +{ + char *str = PG_GETARG_CSTRING(0); - /* not reached */ - PG_RETURN_BOOL(false); + PG_RETURN_BOOL(boolin_opt_error(str, strlen(str), NULL)); } /* diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c index 081dfa2450f..778bfc90687 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -1286,10 +1286,9 @@ date_timestamp(PG_FUNCTION_ARGS) /* timestamp_date() * Convert timestamp to date data type. */ -Datum -timestamp_date(PG_FUNCTION_ARGS) +DateADT +timestamp_date_opt_error(Timestamp timestamp, bool *error) { - Timestamp timestamp = PG_GETARG_TIMESTAMP(0); DateADT result; struct pg_tm tt, *tm = &tt; @@ -1302,16 +1301,32 @@ timestamp_date(PG_FUNCTION_ARGS) else { if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0) + { + if (error) + { + *error = true; + return 0; + } + ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg("timestamp out of range"))); + } result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE; } - PG_RETURN_DATEADT(result); + return result; } +/* timestamp_date() + * Convert timestamp to date data type. + */ +Datum +timestamp_date(PG_FUNCTION_ARGS) +{ + PG_RETURN_DATEADT(timestamp_date_opt_error(PG_GETARG_TIMESTAMP(0), NULL)); +} /* date_timestamptz() * Convert date to timestamp with time zone data type. @@ -1327,14 +1342,9 @@ date_timestamptz(PG_FUNCTION_ARGS) PG_RETURN_TIMESTAMP(result); } - -/* timestamptz_date() - * Convert timestamp with time zone to date data type. - */ -Datum -timestamptz_date(PG_FUNCTION_ARGS) +DateADT +timestamptz_date_opt_error(TimestampTz timestamp, bool *error) { - TimestampTz timestamp = PG_GETARG_TIMESTAMP(0); DateADT result; struct pg_tm tt, *tm = &tt; @@ -1348,14 +1358,31 @@ timestamptz_date(PG_FUNCTION_ARGS) else { if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0) + { + if (error) + { + *error = true; + return 0; + } + ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg("timestamp out of range"))); + } result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE; } - PG_RETURN_DATEADT(result); + return result; +} + +/* timestamptz_date() + * Convert timestamp with time zone to date data type. + */ +Datum +timestamptz_date(PG_FUNCTION_ARGS) +{ + PG_RETURN_DATEADT(timestamptz_date_opt_error(PG_GETARG_TIMESTAMP(0), NULL)); } @@ -1889,22 +1916,32 @@ overlaps_time(PG_FUNCTION_ARGS) /* timestamp_time() * Convert timestamp to time data type. */ -Datum -timestamp_time(PG_FUNCTION_ARGS) +TimeADT +timestamp_time_opt_error(Timestamp timestamp, bool *isnull, bool *error) { - Timestamp timestamp = PG_GETARG_TIMESTAMP(0); TimeADT result; struct pg_tm tt, *tm = &tt; fsec_t fsec; if (TIMESTAMP_NOT_FINITE(timestamp)) - PG_RETURN_NULL(); + { + *isnull = true; + return 0; + } if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0) + { + if (error) + { + *error = *isnull = true; + return (Datum) 0; + } + ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg("timestamp out of range"))); + } /* * Could also do this with time = (timestamp / USECS_PER_DAY * @@ -1912,17 +1949,33 @@ timestamp_time(PG_FUNCTION_ARGS) */ result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) * USECS_PER_SEC) + fsec; + *isnull = false; - PG_RETURN_TIMEADT(result); + return result; } -/* timestamptz_time() - * Convert timestamptz to time data type. +/* timestamp_time() + * Convert timestamp to time data type. */ Datum -timestamptz_time(PG_FUNCTION_ARGS) +timestamp_time(PG_FUNCTION_ARGS) +{ + bool isnull; + TimeADT time = + timestamp_time_opt_error(PG_GETARG_TIMESTAMP(0), &isnull, NULL); + + if (isnull) + PG_RETURN_NULL(); + else + PG_RETURN_TIMEADT(time); +} + +/* timestamptz_time_opt_error() + * Convert timestamptz to time data type. + */ +TimeADT +timestamptz_time_opt_error(TimestampTz timestamp, bool *isnull, bool *error) { - TimestampTz timestamp = PG_GETARG_TIMESTAMP(0); TimeADT result; struct pg_tm tt, *tm = &tt; @@ -1930,12 +1983,23 @@ timestamptz_time(PG_FUNCTION_ARGS) fsec_t fsec; if (TIMESTAMP_NOT_FINITE(timestamp)) - PG_RETURN_NULL(); + { + *isnull = true; + return 0; + } if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0) + { + if (error) + { + *error = *isnull = true; + return 0; + } + ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg("timestamp out of range"))); + } /* * Could also do this with time = (timestamp / USECS_PER_DAY * @@ -1943,8 +2007,25 @@ timestamptz_time(PG_FUNCTION_ARGS) */ result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) * USECS_PER_SEC) + fsec; + *isnull = false; - PG_RETURN_TIMEADT(result); + return result; +} + +/* timestamptz_time() + * Convert timestamptz to time data type. + */ +Datum +timestamptz_time(PG_FUNCTION_ARGS) +{ + bool isnull; + TimeADT result = + timestamptz_time_opt_error(PG_GETARG_TIMESTAMP(0), &isnull, NULL); + + if (isnull) + PG_RETURN_NULL(); + else + PG_RETURN_TIMEADT(result); } /* datetime_timestamp() @@ -2812,10 +2893,9 @@ time_timetz(PG_FUNCTION_ARGS) /* timestamptz_timetz() * Convert timestamp to timetz data type. */ -Datum -timestamptz_timetz(PG_FUNCTION_ARGS) +TimeTzADT * +timestamptz_timetz_opt_error(TimestampTz timestamp, bool *error) { - TimestampTz timestamp = PG_GETARG_TIMESTAMP(0); TimeTzADT *result; struct pg_tm tt, *tm = &tt; @@ -2823,20 +2903,42 @@ timestamptz_timetz(PG_FUNCTION_ARGS) fsec_t fsec; if (TIMESTAMP_NOT_FINITE(timestamp)) - PG_RETURN_NULL(); + return NULL; if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0) + { + if (error) + { + *error = true; + return NULL; + } + ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg("timestamp out of range"))); + } result = (TimeTzADT *) palloc(sizeof(TimeTzADT)); tm2timetz(tm, fsec, tz, result); - PG_RETURN_TIMETZADT_P(result); + return result; } +/* timestamptz_timetz() + * Convert timestamp to timetz data type. + */ +Datum +timestamptz_timetz(PG_FUNCTION_ARGS) +{ + TimeTzADT *result = + timestamptz_timetz_opt_error(PG_GETARG_TIMESTAMP(0), NULL); + + if (result) + PG_RETURN_TIMETZADT_P(result); + else + PG_RETURN_NULL(); +} /* datetimetz_timestamptz() * Convert date and timetz to timestamp with time zone data type. diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index f700c5b4c93..1da519daaef 100644 --- a/src/backend/utils/adt/jsonb.c +++ b/src/backend/utils/adt/jsonb.c @@ -46,7 +46,6 @@ typedef struct JsonbAggState Oid val_output_func; } JsonbAggState; -static inline Datum jsonb_from_cstring(char *json, int len, bool unique_keys); static size_t checkStringLen(size_t len); static void jsonb_in_object_start(void *pstate); static void jsonb_in_object_end(void *pstate); @@ -77,7 +76,7 @@ jsonb_in(PG_FUNCTION_ARGS) { char *json = PG_GETARG_CSTRING(0); - return jsonb_from_cstring(json, strlen(json), false); + PG_RETURN_DATUM(jsonb_from_cstring(json, strlen(json), false, NULL)); } /* @@ -101,7 +100,7 @@ jsonb_recv(PG_FUNCTION_ARGS) else elog(ERROR, "unsupported jsonb version number %d", version); - return jsonb_from_cstring(str, nbytes, false); + return jsonb_from_cstring(str, nbytes, false, NULL); } /* @@ -147,7 +146,7 @@ jsonb_from_text(text *js, bool unique_keys) { return jsonb_from_cstring(VARDATA_ANY(js), VARSIZE_ANY_EXHDR(js), - unique_keys); + unique_keys, NULL); } /* @@ -239,12 +238,13 @@ jsonb_typeof(PG_FUNCTION_ARGS) * * Uses the json parser (with hooks) to construct a jsonb. */ -static inline Datum -jsonb_from_cstring(char *json, int len, bool unique_keys) +Datum +jsonb_from_cstring(char *json, int len, bool unique_keys, bool *error) { JsonLexContext *lex; JsonbInState state; JsonSemAction sem; + JsonParseErrorType result; memset(&state, 0, sizeof(state)); memset(&sem, 0, sizeof(sem)); @@ -261,10 +261,20 @@ jsonb_from_cstring(char *json, int len, bool unique_keys) sem.scalar = jsonb_in_scalar; sem.object_field_start = jsonb_in_object_field_start; - pg_parse_json_or_ereport(lex, &sem); + result = pg_parse_json(lex, &sem); + if (result != JSON_SUCCESS) + { + if (error) + { + *error = true; + return (Datum) 0; + } + + json_ereport_error(result, lex); + } /* after parsing, the item member has the composed jsonb structure */ - PG_RETURN_POINTER(JsonbValueToJsonb(state.res)); + return JsonbPGetDatum(JsonbValueToJsonb(state.res)); } static size_t diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c index 920a63b0081..98b1a9ac9ee 100644 --- a/src/backend/utils/adt/numeric.c +++ b/src/backend/utils/adt/numeric.c @@ -4308,15 +4308,20 @@ int8_numeric(PG_FUNCTION_ARGS) } -Datum -numeric_int8(PG_FUNCTION_ARGS) +int64 +numeric_int8_opt_error(Numeric num, bool *error) { - Numeric num = PG_GETARG_NUMERIC(0); NumericVar x; int64 result; if (NUMERIC_IS_SPECIAL(num)) { + if (error) + { + *error = true; + return 0; + } + if (NUMERIC_IS_NAN(num)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), @@ -4331,13 +4336,26 @@ numeric_int8(PG_FUNCTION_ARGS) init_var_from_num(num, &x); if (!numericvar_to_int64(&x, &result)) + { + if (error) + { + *error = true; + return 0; + } + ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), errmsg("bigint out of range"))); + } - PG_RETURN_INT64(result); + return result; } +Datum +numeric_int8(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(numeric_int8_opt_error(PG_GETARG_NUMERIC(0), NULL)); +} Datum int2_numeric(PG_FUNCTION_ARGS) @@ -4347,17 +4365,20 @@ int2_numeric(PG_FUNCTION_ARGS) PG_RETURN_NUMERIC(int64_to_numeric(val)); } - -Datum -numeric_int2(PG_FUNCTION_ARGS) +int16 +numeric_int2_opt_error(Numeric num, bool *error) { - Numeric num = PG_GETARG_NUMERIC(0); NumericVar x; int64 val; - int16 result; if (NUMERIC_IS_SPECIAL(num)) { + if (error) + { + *error = true; + return 0; + } + if (NUMERIC_IS_NAN(num)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), @@ -4372,21 +4393,40 @@ numeric_int2(PG_FUNCTION_ARGS) init_var_from_num(num, &x); if (!numericvar_to_int64(&x, &val)) + { + if (error) + { + *error = true; + return 0; + } + ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), errmsg("smallint out of range"))); + } if (unlikely(val < PG_INT16_MIN) || unlikely(val > PG_INT16_MAX)) + { + if (error) + { + *error = true; + return 0; + } + ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), errmsg("smallint out of range"))); + } /* Down-convert to int2 */ - result = (int16) val; - - PG_RETURN_INT16(result); + return (int16) val; } +Datum +numeric_int2(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT16(numeric_int2_opt_error(PG_GETARG_NUMERIC(0), NULL)); +} Datum float8_numeric(PG_FUNCTION_ARGS) diff --git a/src/backend/utils/adt/numutils.c b/src/backend/utils/adt/numutils.c index cc3f95d3990..b82b30f9013 100644 --- a/src/backend/utils/adt/numutils.c +++ b/src/backend/utils/adt/numutils.c @@ -248,7 +248,7 @@ invalid_syntax: * positive number. */ int64 -pg_strtoint64(const char *s) +pg_strtoint64_opt_error(const char *s, bool *error) { const char *ptr = s; int64 tmp = 0; @@ -307,12 +307,24 @@ pg_strtoint64(const char *s) return tmp; out_of_range: + if (error) + { + *error = true; + return 0; + } + ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), errmsg("value \"%s\" is out of range for type %s", s, "bigint"))); invalid_syntax: + if (error) + { + *error = true; + return 0; + } + ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input syntax for type %s: \"%s\"", @@ -321,6 +333,12 @@ invalid_syntax: return 0; /* keep compiler quiet */ } +int64 +pg_strtoint64(const char *s) +{ + return pg_strtoint64_opt_error(s, NULL); +} + /* * pg_itoa: converts a signed 16-bit integer to its string representation * and returns strlen(a). diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index 49cdb290ac2..889d3476e56 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -5614,8 +5614,8 @@ timestamptz_timestamp(PG_FUNCTION_ARGS) PG_RETURN_TIMESTAMP(timestamptz2timestamp(timestamp)); } -static Timestamp -timestamptz2timestamp(TimestampTz timestamp) +Timestamp +timestamptz2timestamp_opt_error(TimestampTz timestamp, bool *error) { Timestamp result; struct pg_tm tt, @@ -5628,17 +5628,40 @@ timestamptz2timestamp(TimestampTz timestamp) else { if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0) + { + if (error) + { + *error = true; + return 0; + } + ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg("timestamp out of range"))); + } + if (tm2timestamp(tm, fsec, NULL, &result) != 0) + { + if (error) + { + *error = true; + return 0; + } + ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg("timestamp out of range"))); + } } return result; } +static Timestamp +timestamptz2timestamp(TimestampTz timestamp) +{ + return timestamptz2timestamp_opt_error(timestamp, NULL); +} + /* timestamptz_zone() * Evaluate timestamp with time zone type at the specified time zone. * Returns a timestamp without time zone. diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index c8ef917ffe0..adabf57c97b 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -762,43 +762,14 @@ typedef struct JsonExprState { JsonExpr *jsexpr; /* original expression node */ - struct - { - FmgrInfo func; /* typinput function for output type */ - Oid typioparam; - } input; /* I/O info for output type */ - NullableDatum - *formatted_expr, /* formatted context item value */ - *res_expr, /* result item */ - *coercion_expr, /* input for JSON item coercion */ - *pathspec; /* path specification value */ + formatted_expr, /* formatted context item value */ + coercion_expr, /* input for JSON item coercion */ + pathspec; /* path specification value */ - ExprState *result_expr; /* coerced to output type */ ExprState *default_on_empty; /* ON EMPTY DEFAULT expression */ ExprState *default_on_error; /* ON ERROR DEFAULT expression */ List *args; /* passing arguments */ - - void *cache; /* cache for json_populate_type() */ - - struct JsonCoercionsState - { - struct JsonCoercionState - { - JsonCoercion *coercion; /* coercion expression */ - ExprState *estate; /* coercion expression state */ - } null, - string, - numeric , - boolean, - date, - time, - timetz, - timestamp, - timestamptz, - composite; - } coercions; /* states for coercion from SQL/JSON item - * types directly to the output type */ } JsonExprState; /* functions in execExpr.c */ @@ -860,18 +831,8 @@ extern void ExecEvalSysVar(ExprState *state, ExprEvalStep *op, ExprContext *econtext, TupleTableSlot *slot); extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op, ExprContext *econtext); -extern void ExecEvalJson(ExprState *state, ExprEvalStep *op, - ExprContext *econtext); -extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item, - JsonReturning *returning, - struct JsonCoercionsState *coercions, - struct JsonCoercionState **pjcstate); -extern bool ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr, - struct JsonCoercionsState *); -extern Datum ExecEvalExprPassingCaseValue(ExprState *estate, - ExprContext *econtext, bool *isnull, - Datum caseval_datum, - bool caseval_isnull); +extern void ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, + ExprContext *econtext); extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup, ExprContext *aggcontext); diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 3aa96bb6855..14fbb32ce9c 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1551,39 +1551,6 @@ typedef struct JsonBehavior Node *default_expr; /* default expression, if any */ } JsonBehavior; -/* - * JsonCoercion - - * coercion from SQL/JSON item types to SQL types - */ -typedef struct JsonCoercion -{ - NodeTag type; - Node *expr; /* resulting expression coerced to target type */ - bool via_populate; /* coerce result using json_populate_type()? */ - bool via_io; /* coerce result using type input function? */ - Oid collation; /* collation for coercion via I/O or populate */ -} JsonCoercion; - -/* - * JsonItemCoercions - - * expressions for coercion from SQL/JSON item types directly to the - * output SQL type - */ -typedef struct JsonItemCoercions -{ - NodeTag type; - JsonCoercion *null; - JsonCoercion *string; - JsonCoercion *numeric; - JsonCoercion *boolean; - JsonCoercion *date; - JsonCoercion *time; - JsonCoercion *timetz; - JsonCoercion *timestamp; - JsonCoercion *timestamptz; - JsonCoercion *composite; /* arrays and objects */ -} JsonItemCoercions; - /* * JsonExpr - * transformed representation of JSON_VALUE(), JSON_QUERY(), JSON_EXISTS() @@ -1593,7 +1560,6 @@ typedef struct JsonExpr Expr xpr; JsonExprOp op; /* json function ID */ Node *formatted_expr; /* formatted context item expression */ - JsonCoercion *result_coercion; /* resulting coercion to RETURNING type */ JsonFormat *format; /* context item format (JSON/JSONB) */ Node *path_spec; /* JSON path specification expression */ List *passing_names; /* PASSING argument names */ @@ -1601,8 +1567,8 @@ typedef struct JsonExpr JsonReturning *returning; /* RETURNING clause type/format info */ JsonBehavior *on_empty; /* ON EMPTY behavior */ JsonBehavior *on_error; /* ON ERROR behavior */ - JsonItemCoercions *coercions; /* coercions for JSON_VALUE */ JsonWrapper wrapper; /* WRAPPER for JSON_QUERY */ + Oid collation; /* OID of collation, or InvalidOid if none */ bool omit_quotes; /* KEEP/OMIT QUOTES for JSON_QUERY */ int location; /* token location, or -1 if unknown */ } JsonExpr; diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 221c3e6c3de..5e1aa37c1f5 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -24,6 +24,7 @@ /* bool.c */ extern bool parse_bool(const char *value, bool *result); extern bool parse_bool_with_len(const char *value, size_t len, bool *result); +extern bool boolin_opt_error(const char *value, size_t len, bool *error); /* domains.c */ extern void domain_check(Datum value, bool isnull, Oid domainType, @@ -46,6 +47,7 @@ extern int namestrcmp(Name name, const char *str); extern int16 pg_strtoint16(const char *s); extern int32 pg_strtoint32(const char *s); extern int64 pg_strtoint64(const char *s); +extern int64 pg_strtoint64_opt_error(const char *s, bool *error); extern int pg_itoa(int16 i, char *a); extern int pg_ultoa_n(uint32 l, char *a); extern int pg_ulltoa_n(uint64 l, char *a); diff --git a/src/include/utils/date.h b/src/include/utils/date.h index 91ae24254df..9059f2b5ce7 100644 --- a/src/include/utils/date.h +++ b/src/include/utils/date.h @@ -87,4 +87,14 @@ extern bool time_overflows(int hour, int min, int sec, fsec_t fsec); extern bool float_time_overflows(int hour, int min, double sec); extern void AdjustTimeForTypmod(TimeADT *time, int32 typmod); +extern DateADT timestamp_date_opt_error(Timestamp timestamp, bool *error); +extern TimeADT timestamp_time_opt_error(Timestamp timestamp, + bool *isnull, bool *error); + +extern DateADT timestamptz_date_opt_error(Timestamp timestamp, bool *error); +extern TimeADT timestamptz_time_opt_error(TimestampTz timestamp, + bool *isnull, bool *error); +extern TimeTzADT *timestamptz_timetz_opt_error(TimestampTz timestamp, + bool *error); + #endif /* DATE_H */ diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index bae466b5234..d5574faa79e 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -420,6 +420,8 @@ extern void JsonbHashScalarValueExtended(const JsonbValue *scalarVal, /* jsonb.c support functions */ extern Datum jsonb_from_text(text *js, bool unique_keys); +extern Datum jsonb_from_cstring(char *json, int len, bool unique_keys, + bool *error); extern char *JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len); extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in, diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h index 3caa74dfe7a..ede68ab81b8 100644 --- a/src/include/utils/numeric.h +++ b/src/include/utils/numeric.h @@ -85,6 +85,8 @@ extern Numeric numeric_div_opt_error(Numeric num1, Numeric num2, bool *have_error); extern Numeric numeric_mod_opt_error(Numeric num1, Numeric num2, bool *have_error); -extern int32 numeric_int4_opt_error(Numeric num, bool *error); +extern int16 numeric_int2_opt_error(Numeric num, bool *have_error); +extern int32 numeric_int4_opt_error(Numeric num, bool *have_error); +extern int64 numeric_int8_opt_error(Numeric num, bool *have_error); #endif /* _PG_NUMERIC_H_ */ diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h index edf3a973186..f9382d7193f 100644 --- a/src/include/utils/timestamp.h +++ b/src/include/utils/timestamp.h @@ -102,6 +102,8 @@ extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2); extern TimestampTz timestamp2timestamptz_opt_overflow(Timestamp timestamp, int *overflow); +extern Timestamp timestamptz2timestamp_opt_error(TimestampTz timestamp, + bool *error); extern int32 timestamp_cmp_timestamptz_internal(Timestamp timestampVal, TimestampTz dt2); diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out index ef496110af3..0d0705e6a8f 100644 --- a/src/test/regress/expected/jsonb_sqljson.out +++ b/src/test/regress/expected/jsonb_sqljson.out @@ -184,11 +184,11 @@ SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR); (1 row) SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb); -ERROR: cannot cast type boolean to jsonb +ERROR: returning type jsonb is not supported in JSON_EXISTS() LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb); ^ SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4); -ERROR: cannot cast type boolean to real +ERROR: returning type real is not supported in JSON_EXISTS() LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4); ^ -- JSON_VALUE @@ -242,7 +242,9 @@ SELECT JSON_VALUE(jsonb '123', '$' RETURNING text); /* jsonb bytea ??? */ SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ERROR); -ERROR: SQL/JSON item cannot be cast to target type +ERROR: returning type bytea is not supported in JSON_VALUE() +LINE 2: SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ... + ^ SELECT JSON_VALUE(jsonb '1.23', '$'); json_value ------------ @@ -349,14 +351,213 @@ SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9; 03-01-2017 (1 row) --- Test NULL checks execution in domain types +-- Test for domain types CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL; +CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0'); +CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0'); +-- Test casting to json[b] domains (items casted as is, strings are not unquoted) +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_n... + ^ +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_... + ^ +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_json_not_null); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_json_not... + ^ +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_jsonb_no... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb... + ^ +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_json_not_null); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_json_... + ^ +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_jsonb... + ^ +-- Test NULL checks execution in domain types SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null); -ERROR: domain sqljsonb_int_not_null does not allow null values +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY); +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY DEFAULT 2 ON ERROR); +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no... + ^ SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR); -ERROR: domain sqljsonb_int_not_null does not allow null values +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no... + ^ SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); -ERROR: domain sqljsonb_int_not_null does not allow null values +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null NULL ON ERROR); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_... + ^ +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null); +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_n... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_n... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_n... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null NULL ON ERROR); +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_n... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_n... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null NULL ON ERROR); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb... + ^ +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb... + ^ +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not... + ^ +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null ERROR ON ERROR); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not... + ^ +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); +ERROR: returning type sqljsonb_json_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not... + ^ +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_no... + ^ +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null ERROR ON ERROR); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_no... + ^ +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); +ERROR: returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_no... + ^ +-- Test returning of non-scalar items SELECT JSON_VALUE(jsonb '[]', '$'); json_value ------------ @@ -478,11 +679,9 @@ SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a); (1 row) SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point); - json_value ------------- - (1,2) -(1 row) - +ERROR: returning type point is not supported in JSON_VALUE() +LINE 1: SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )... + ^ -- Test timestamptz passing and output SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts); json_value @@ -852,49 +1051,35 @@ FROM CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]); CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]); SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljsonb_rec); - json_query ------------------------------------------------------ - (1,aaa,"[1, ""2"", {}]","{""x"": [1, ""2"", {}]}",) -(1 row) - +ERROR: returning type sqljsonb_rec is not supported in JSON_QUERY() +LINE 1: SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "... + ^ SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa": [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljsonb_rec)).jsa); - unnest ------------------------- - {"a": 1, "b": ["foo"]} - {"a": 2, "c": {}} - 123 -(3 rows) - +ERROR: returning type sqljsonb_rec is not supported in JSON_QUERY() +LINE 1: SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa": [{"a": 1, "... + ^ SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljsonb_reca)).reca); - a | t | js | jb | jsa ----+-------------+----+------------+----- - 1 | ["foo", []] | | | - 2 | | | [{}, true] | -(2 rows) - +ERROR: returning type sqljsonb_reca is not supported in JSON_QUERY() +LINE 1: SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "... + ^ -- Extension: array types returning SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER); - json_query --------------- - {1,2,NULL,3} -(1 row) - +ERROR: returning type integer[] is not supported in JSON_QUERY() +LINE 1: SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING i... + ^ SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljsonb_rec[])); - a | t | js | jb | jsa ----+-------------+----+------------+----- - 1 | ["foo", []] | | | - 2 | | | [{}, true] | -(2 rows) - +ERROR: returning type sqljsonb_rec[] is not supported in JSON_QUERY() +LINE 1: SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo"... + ^ -- Extension: domain types returning SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null); - json_query ------------- - 1 -(1 row) - +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_QUERY() +LINE 1: SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb... + ^ SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null); -ERROR: domain sqljsonb_int_not_null does not allow null values +ERROR: returning type sqljsonb_int_not_null is not supported in JSON_QUERY() +LINE 1: SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb... + ^ -- Test timestamptz passing and output SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts); json_query @@ -1048,7 +1233,6 @@ SELECT * FROM JSON_TABLE(jsonb '123', '$' (1 row) -- JSON_TABLE: basic functionality -CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo'); SELECT * FROM (VALUES @@ -1069,7 +1253,6 @@ FROM "char(4)" char(4) PATH '$', "bool" bool PATH '$', "numeric" numeric PATH '$', - "domain" jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', @@ -1085,10 +1268,7 @@ FROM exists4 text EXISTS PATH 'strict $.aaa' FALSE ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH WRAPPER, - jsb2q jsonb PATH '$' OMIT QUOTES, - ia int[] PATH '$', - ta text[] PATH '$', - jba jsonb[] PATH '$' + jsb2q jsonb PATH '$' OMIT QUOTES ) ) jt ON true; @@ -1123,7 +1303,6 @@ SELECT * FROM "char(4)" char(4) PATH '$', "bool" bool PATH '$', "numeric" numeric PATH '$', - "domain" jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', @@ -1139,9 +1318,6 @@ SELECT * FROM js2 json PATH '$', jsb2w jsonb PATH '$' WITH WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, - ia int[] PATH '$', - ta text[] PATH '$', - jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS ( a1 int, NESTED PATH '$[*]' AS "p1 1" COLUMNS ( @@ -1168,7 +1344,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS "json_table"."char(4)", "json_table".bool, "json_table"."numeric", - "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, @@ -1184,9 +1359,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, - "json_table".ia, - "json_table".ta, - "json_table".jba, "json_table".a1, "json_table".b1, "json_table".a11, @@ -1205,7 +1377,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', - domain jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', @@ -1221,9 +1392,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, - ia integer[] PATH '$', - ta text[] PATH '$', - jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS ( a1 integer PATH '$."a1"', @@ -1248,15 +1416,14 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))) ) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Table Function Scan on "json_table" - Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22 - Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))) + Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22 + Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))) (3 rows) DROP VIEW jsonb_table_view; -DROP DOMAIN jsonb_test_domain; -- JSON_TABLE: ON EMPTY/ON ERROR behavior SELECT * FROM @@ -1318,15 +1485,15 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a')); (1 row) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a')); -ERROR: cannot cast type boolean to smallint +ERROR: returning type smallint is not supported in JSON_EXISTS() LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXI... ^ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a')); -ERROR: cannot cast type boolean to bigint +ERROR: returning type bigint is not supported in JSON_EXISTS() LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXI... ^ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a')); -ERROR: cannot cast type boolean to real +ERROR: returning type real is not supported in JSON_EXISTS() LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 E... ^ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a')); @@ -1336,11 +1503,11 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a')) (1 row) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a')); -ERROR: cannot cast type boolean to json +ERROR: returning type json is not supported in JSON_EXISTS() LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXI... ^ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a')); -ERROR: cannot cast type boolean to jsonb +ERROR: returning type jsonb is not supported in JSON_EXISTS() LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EX... ^ -- JSON_TABLE: nested paths and plans @@ -2102,11 +2269,14 @@ set parallel_leader_participation = off; -- Should be non-parallel due to subtransactions EXPLAIN (COSTS OFF) SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value; - QUERY PLAN ---------------------------------------------- - Aggregate - -> Seq Scan on test_parallel_jsonb_value -(2 rows) + QUERY PLAN +------------------------------------------------------------------ + Finalize Aggregate + -> Gather + Workers Planned: 4 + -> Partial Aggregate + -> Parallel Seq Scan on test_parallel_jsonb_value +(5 rows) SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value; sum diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql index fff25374808..e74a4a5dbbe 100644 --- a/src/test/regress/sql/jsonb_sqljson.sql +++ b/src/test/regress/sql/jsonb_sqljson.sql @@ -82,12 +82,73 @@ SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234; SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9; --- Test NULL checks execution in domain types +-- Test for domain types CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL; +CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0'); +CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0'); + +-- Test casting to json[b] domains (items casted as is, strings are not unquoted) +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_jsonb_not_null); + +-- Test NULL checks execution in domain types SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY DEFAULT 2 ON ERROR); SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR); SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR); +SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR); + +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null ERROR ON ERROR); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR); + +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null ERROR ON ERROR); +SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR); + +-- Test returning of non-scalar items SELECT JSON_VALUE(jsonb '[]', '$'); SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR); SELECT JSON_VALUE(jsonb '{}', '$'); @@ -338,8 +399,6 @@ SELECT * FROM JSON_TABLE(jsonb '123', '$' COLUMNS (item int PATH '$', foo int)) bar; -- JSON_TABLE: basic functionality -CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo'); - SELECT * FROM (VALUES @@ -360,7 +419,6 @@ FROM "char(4)" char(4) PATH '$', "bool" bool PATH '$', "numeric" numeric PATH '$', - "domain" jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', @@ -377,10 +435,7 @@ FROM js2 json PATH '$', jsb2w jsonb PATH '$' WITH WRAPPER, - jsb2q jsonb PATH '$' OMIT QUOTES, - ia int[] PATH '$', - ta text[] PATH '$', - jba jsonb[] PATH '$' + jsb2q jsonb PATH '$' OMIT QUOTES ) ) jt ON true; @@ -399,7 +454,6 @@ SELECT * FROM "char(4)" char(4) PATH '$', "bool" bool PATH '$', "numeric" numeric PATH '$', - "domain" jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', @@ -416,9 +470,6 @@ SELECT * FROM js2 json PATH '$', jsb2w jsonb PATH '$' WITH WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, - ia int[] PATH '$', - ta text[] PATH '$', - jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS ( a1 int, @@ -443,7 +494,6 @@ SELECT * FROM EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; DROP VIEW jsonb_table_view; -DROP DOMAIN jsonb_test_domain; -- JSON_TABLE: ON EMPTY/ON ERROR behavior SELECT * -- 2.17.1