pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Andrew Dunstan <andrew@dunslane.net>
To: Jonathan S. Katz <jkatz@postgresql.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: John Naylor <john.naylor@enterprisedb.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Subject: Re: SQL/JSON features for v15
Date: Fri, 26 Aug 2022 23:11:14 +0300
Message-ID: <6ba7ba7b-9fdd-674f-c227-59a59e0de1ec@postgrespro.ru> (raw )
In-Reply-To: <59660a0b-7b7a-75ab-75c4-b6d53fbda5f6@dunslane.net >
References: <abd9b83b-aa66-f230-3d6d-734817f0995d@postgresql.org >
<20220816021425.5sa6gx67zmpou3bc@awork3.anarazel.de >
<8d0ffc54-469a-ffab-68a3-8b547e24a08d@postgresql.org >
<7d83684b-7932-9f29-400b-0beedfafcdd4@postgrespro.ru >
<69022273-e7e3-6d1a-10f8-d1b4740e4b97@postgresql.org >
<92cab50c-152b-84fc-fd49-12b936113a3d@postgresql.org >
<CA+TgmoaAZq6rRdVA65PBXBKs=jUHhf42Jf78kBLOaX3c6J7MfQ@mail.gmail.com >
<692364.1661267311@sss.pgh.pa.us >
<35468585-e18f-3be6-a228-4ec478f796ac@postgresql.org >
<716936.1661275451@sss.pgh.pa.us >
<f1c79751-2916-a9cf-c8b9-668b9700edb8@dunslane.net >
<ab10b5df-fcc2-5871-7027-e7f73b11559d@postgresql.org >
<3596205e-ce1e-6959-7559-6ddd67f8294b@postgrespro.ru >
<cc17d718-56a1-359f-2792-01c5debd67e3@dunslane.net >
<3f8d4c2b-1e58-bf19-580e-64e9755897ef@postgrespro.ru >
<072a47f0-7a39-2266-5f90-1178a3860be1@dunslane.net >
<1992b190-f00b-8435-b561-e3c6ac450482@postgresql.org >
<59660a0b-7b7a-75ab-75c4-b6d53fbda5f6@dunslane.net >
Hi,
On 26.08.2022 22:25, Andrew Dunstan wrote:
> On 2022-08-24 We 20:05, Nikita Glukhov wrote:
>> v8 - is a highly WIP patch, which I failed to finish today.
>> Even some test cases fail now, and they simply show unfinished
>> things like casts to bytea (they can be simply removed) and missing
>> safe input functions.
> Thanks for your work, please keep going.
I have completed in v9 all the things I previously planned:
- Added missing safe I/O and type conversion functions for
datetime, float4, varchar, bpchar. This introduces a lot
of boilerplate code for returning errors and also maybe
adds some overhead.
- Added JSON_QUERY coercion to UTF8 bytea using pg_convert_to().
- Added immutability checks that were missed with elimination
of coercion expressions.
Coercions text::datetime, datetime1::datetime2 and even
datetime::text for some datetime types are mutable.
datetime::text can be made immutable by passing ISO date
style into output functions (like in jsonpath).
- Disabled non-Const expressions in DEFAULT ON EMPTY in non
ERROR ON ERROR case. Non-constant expressions are tried to
evaluate into Const directly inside transformExpr().
Maybe it would be better to simply remove DEFAULT ON EMPTY.
It is possible to easily split this patch into several subpatches,
I will do it if needed.
--
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
Attachments:
v9-0001-Remove-subtransactions-in-SQL-JSON-execution.patch (text/x-patch, 150.3K) download
From 95417c337c2ab6899df28017641e5342cb925a11 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Fri, 26 Aug 2022 22:46:02 +0300
Subject: [PATCH v9] Remove subtransactions in SQL/JSON execution
---
contrib/adminpack/adminpack.c | 2 +-
src/backend/executor/execExpr.c | 84 +-
src/backend/executor/execExprInterp.c | 946 ++++++++++++--------
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 | 73 +-
src/backend/parser/parse_expr.c | 226 ++---
src/backend/utils/adt/bool.c | 31 +-
src/backend/utils/adt/date.c | 277 ++++--
src/backend/utils/adt/datetime.c | 16 +-
src/backend/utils/adt/float.c | 70 +-
src/backend/utils/adt/jsonb.c | 26 +-
src/backend/utils/adt/jsonpath.c | 8 +-
src/backend/utils/adt/numeric.c | 230 +++--
src/backend/utils/adt/numutils.c | 20 +-
src/backend/utils/adt/timestamp.c | 126 ++-
src/backend/utils/misc/guc.c | 2 +-
src/include/executor/execExpr.h | 49 +-
src/include/nodes/primnodes.h | 36 +-
src/include/optimizer/clauses.h | 2 +
src/include/utils/builtins.h | 2 +
src/include/utils/date.h | 13 +
src/include/utils/datetime.h | 6 +-
src/include/utils/float.h | 1 +
src/include/utils/jsonb.h | 2 +
src/include/utils/jsonpath.h | 3 +-
src/include/utils/numeric.h | 6 +-
src/include/utils/timestamp.h | 6 +
src/test/regress/expected/jsonb_sqljson.out | 327 +++++--
src/test/regress/sql/jsonb_sqljson.sql | 72 +-
31 files changed, 1704 insertions(+), 1053 deletions(-)
diff --git a/contrib/adminpack/adminpack.c b/contrib/adminpack/adminpack.c
index 03addf1dc5f..4bf795d42a4 100644
--- a/contrib/adminpack/adminpack.c
+++ b/contrib/adminpack/adminpack.c
@@ -571,7 +571,7 @@ pg_logdir_ls_internal(FunctionCallInfo fcinfo)
if (ParseDateTime(timestampbuf, lowstr, MAXDATELEN, field, ftype, MAXDATEFIELDS, &nf))
continue;
- if (DecodeDateTime(field, ftype, nf, &dtype, &date, &fsec, &tz))
+ if (DecodeDateTime(field, ftype, nf, &dtype, &date, &fsec, &tz, true))
continue;
/* Seems the timestamp is OK; prepare and return tuple */
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..45c648817ba 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -75,6 +75,7 @@
#include "utils/date.h"
#include "utils/datum.h"
#include "utils/expandedrecord.h"
+#include "utils/float.h"
#include "utils/json.h"
#include "utils/jsonb.h"
#include "utils/jsonfuncs.h"
@@ -1844,7 +1845,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 +4701,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 +4755,438 @@ 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;
+}
- if (item->type == jbvBinary &&
- JsonContainerIsScalar(item->val.binary.data))
+/* Coerce C string to text, varchar(N), or bpchar(N) */
+static Datum
+ExecJsonStringCoercion(const char *str, int32 len, Oid typid, int32 typmod)
+{
+ if (typid == BYTEAOID)
{
- bool res PG_USED_FOR_ASSERTS_ONLY;
+ text *txt = cstring_to_text_with_len(str, len);
+ NameData encoding = {0};
+
+ /* UTF8 is the only one supported encoding */
+ strcpy(NameStr(encoding), "UTF8");
- res = JsonbExtractScalar(item->val.binary.data, &buf);
- item = &buf;
- Assert(res);
+ return DirectFunctionCall2(pg_convert_to,
+ PointerGetDatum(txt),
+ PointerGetDatum(&encoding));
}
+ else 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 (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);
- /* get coercion state reference and datum of the corresponding SQL type */
+ 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;
+
+ /*
+ * XXX coercion to text is done using output functions, and they
+ * are mutable for non-time[tz] types due to using of DateStyle.
+ * We can pass USE_ISO_DATES, which is used inside jsonpath, to
+ * make these coercions and JSON_VALUE(RETURNING text) immutable.
+ *
+ * XXX Also timestamp[tz] output functions can throw "out of range"
+ * error, but this error seem to be not possible.
+ */
+ switch (val_typid)
+ {
+ 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;
+ }
+
+ 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;
+ }
+
+ 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,
- item->val.string.len));
- break;
+ 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
+ {
+ char *str = pnstrdup(item->val.string.val,
+ item->val.string.len);
+
+ if (typid == FLOAT4OID)
+ return Float4GetDatum(float4in_opt_error(str, error));
+ else if (typid == FLOAT8OID)
+ return Float8GetDatum(
+ float8in_internal_opt_error(str, NULL,
+ "double precision",
+ str, error));
+ else if (typid == NUMERICOID)
+ return NumericGetDatum(numeric_in_opt_error(str, typmod, error));
+ else if (typid == DATEOID)
+ return DateADTGetDatum(date_in_opt_error(str, error));
+ else if (typid == TIMEOID)
+ return TimeADTGetDatum(time_in_opt_error(str, typmod, error));
+ else if (typid == TIMETZOID)
+ return TimeTzADTPGetDatum(timetz_in_opt_error(str, typmod, error));
+ else if (typid == TIMESTAMPOID)
+ return TimestampGetDatum(timestamp_in_opt_error(str, typmod, error));
+ else if (typid == TIMESTAMPTZOID)
+ return TimestampTzGetDatum(timestamptz_in_opt_error(str, typmod, error));
+ 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 if (typid == FLOAT4OID || typid == FLOAT8OID)
+ {
+ /*
+ * XXX numeric_float8() also uses I/O coercion, but
+ * it has special handling of Inf and NaN.
+ */
+ Datum cstr = DirectFunctionCall1(numeric_out,
+ NumericGetDatum(num));
+ char *str = DatumGetCString(cstr);
- case jbvBool:
- coercion = &coercions->boolean;
- res = BoolGetDatum(item->val.boolean);
- break;
+ if (typid == FLOAT4OID)
+ return Float4GetDatum(float4in_opt_error(str, error));
+ else
+ return Float8GetDatum(
+ float8in_internal_opt_error(str, NULL,
+ "double precision",
+ str, error));
+ }
+ else
+ break; /* No cast */
+ }
- case jbvDatetime:
- res = item->val.datetime.value;
- switch (item->val.datetime.typid)
+ case jbvBool:
{
- 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 +5194,100 @@ 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 ||
+ ret_typid == BYTEAOID);
- 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 ||
+ ret_typid == BYTEAOID);
- 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 checked 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 +5295,96 @@ 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 ||
+ ret_typid == BYTEAOID)
+ 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 ||
+ ret_typid == BYTEAOID)
+ {
+ 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);
-
- *resnull = false;
+ if (!jbv)
+ return (Datum) 0; /* NULL */
- /* 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;
- }
+ Assert(jbv->type != jbvNull);
- /* Use coercion from SQL/JSON item type to the output type */
- res = ExecPrepareJsonItemCoercion(jbv,
- jsestate->jsexpr->returning,
- &jsestate->coercions,
- &jcstate);
+ res = ExecJsonValueCoercion(jbv, ret_typid, ret_typmod,
+ resnull, error);
- 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 +5393,68 @@ 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);
-}
-
-bool
-ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr,
- struct JsonCoercionsState *coercions)
-{
- if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
- return false;
-
- if (jsexpr->op == JSON_EXISTS_OP && !jsexpr->result_coercion)
- return false;
-
- if (!coercions)
- return true;
-
- return false;
+ /* Execute ON EMPTY behavior */
+ return ExecEvalJsonBehavior(econtext, jexpr->on_empty,
+ jsestate->default_on_empty,
+ ret_typid, ret_typmod, resnull);
}
/* ----------------------------------------------------------------
- * 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 +5465,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..6eb923892f4 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -411,6 +411,26 @@ contain_mutable_functions_walker(Node *node, void *context)
{
JsonExpr *jexpr = castNode(JsonExpr, node);
Const *cnst;
+ bool returns_datetime;
+
+ /*
+ * Input fuctions for datetime types are stable. They can be
+ * called in JSON_VALUE(), when the resulting SQL/JSON is a
+ * string.
+ */
+ if (jexpr->returning->typid == DATEOID ||
+ jexpr->returning->typid == TIMEOID ||
+ jexpr->returning->typid == TIMETZOID ||
+ jexpr->returning->typid == TIMESTAMPOID ||
+ jexpr->returning->typid == TIMESTAMPTZOID)
+ return true;
+
+ /*
+ * pg_convert_to(), which is used for implementation of
+ * JSON_QUERY(RETURNING bytea FORMAT JSON), is stable.
+ */
+ if (jexpr->returning->typid == BYTEAOID)
+ return true;
if (!IsA(jexpr->path_spec, Const))
return true;
@@ -421,8 +441,32 @@ contain_mutable_functions_walker(Node *node, void *context)
if (cnst->constisnull)
return false;
- return jspIsMutable(DatumGetJsonPathP(cnst->constvalue),
- jexpr->passing_names, jexpr->passing_values);
+ if (jspIsMutable(DatumGetJsonPathP(cnst->constvalue),
+ jexpr->passing_names, jexpr->passing_values,
+ &returns_datetime))
+ return true;
+
+ if (returns_datetime && jexpr->op == JSON_VALUE_OP)
+ {
+ /*
+ * Some datetime types have mutable output functions,
+ * so if returning string types whole expression is mutable.
+ * TODO check individual datetime type if it is known.
+ *
+ * Other non-JSON output types have no conversion from
+ * datetime types.
+ *
+ * Datetime returning types were checked above, no need to
+ * check them here. But conversion between them also can
+ * be mutable.
+ */
+ if (jexpr->returning->typid == TEXTOID ||
+ jexpr->returning->typid == VARCHAROID ||
+ jexpr->returning->typid == BPCHAROID)
+ return true;
+ }
+
+ return false;
}
if (IsA(node, SQLValueFunction))
@@ -896,18 +940,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,
@@ -5323,3 +5355,16 @@ pull_paramids_walker(Node *node, Bitmapset **context)
return expression_tree_walker(node, pull_paramids_walker,
(void *) context);
}
+
+bool
+expr_can_throw_errors(Node *expr)
+{
+ if (!expr)
+ return false;
+
+ if (IsA(expr, Const))
+ return false;
+
+ /* TODO consider more cases */
+ return true;
+}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index fabb5f72076..01c2fdf3859 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -22,6 +22,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "optimizer/clauses.h"
#include "optimizer/optimizer.h"
#include "parser/analyze.h"
#include "parser/parse_agg.h"
@@ -4108,7 +4109,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 +4169,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 +4176,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 +4189,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:
+ case FLOAT8OID:
+ 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 +4232,23 @@ 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:
+ case BYTEAOID:
+ 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
@@ -4259,8 +4260,10 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
* Coerce an expression in JSON DEFAULT behavior to the target output type.
*/
static Node *
-coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, Node *defexpr)
+coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr,
+ bool is_on_empty, Node *defexpr)
{
+ Node *orig_defexpr = defexpr;
int location;
Oid exprtype;
@@ -4290,63 +4293,31 @@ coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, Node *defexpr)
format_type_be(jsexpr->returning->typid)),
parser_errposition(pstate, location)));
- return defexpr;
-}
+ /* Don't allow unsafe DEFAUL ON EMPTY expressions, unless ERROR ON ERROR */
+ if (!is_on_empty ||
+ jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+ 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 (!expr_can_throw_errors(defexpr))
+ return defexpr;
- if (typid == UNKNOWNOID)
+ /* Try to simplify expression if there is non-empty coercion */
+ if (defexpr != orig_defexpr &&
+ !expr_can_throw_errors(orig_defexpr))
{
- expr = (Node *) makeNullConst(UNKNOWNOID, -1, InvalidOid);
- }
- else
- {
- CaseTestExpr *placeholder = makeNode(CaseTestExpr);
+ defexpr = eval_const_expressions(NULL, defexpr);
- placeholder->typeId = typid;
- placeholder->typeMod = -1;
- placeholder->collation = InvalidOid;
-
- expr = (Node *) placeholder;
+ if (!expr_can_throw_errors(defexpr))
+ return defexpr;
}
- return coerceJsonExpr(pstate, expr, returning);
-}
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("unsafe DEFAULT ON EMPTY expressions are not supported"),
+ errhint("Use ERROR ON ERROR clause or try to simplify expression into constant-like form"),
+ parser_errposition(pstate, location)));
-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);
+ return NULL;
}
/*
@@ -4370,17 +4341,13 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
jsexpr->returning->format->encoding = JS_ENC_DEFAULT;
jsexpr->on_empty->default_expr =
- coerceDefaultJsonExpr(pstate, jsexpr,
+ coerceDefaultJsonExpr(pstate, jsexpr, true,
jsexpr->on_empty->default_expr);
jsexpr->on_error->default_expr =
- coerceDefaultJsonExpr(pstate, jsexpr,
+ coerceDefaultJsonExpr(pstate, jsexpr, false,
jsexpr->on_error->default_expr);
- jsexpr->coercions = makeNode(JsonItemCoercions);
- initJsonItemCoercions(pstate, jsexpr->coercions, jsexpr->returning,
- exprType(contextItemExpr));
-
break;
case JSON_QUERY_OP:
@@ -4389,11 +4356,11 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
transformJsonFuncExprOutput(pstate, func, jsexpr);
jsexpr->on_empty->default_expr =
- coerceDefaultJsonExpr(pstate, jsexpr,
+ coerceDefaultJsonExpr(pstate, jsexpr, true,
jsexpr->on_empty->default_expr);
jsexpr->on_error->default_expr =
- coerceDefaultJsonExpr(pstate, jsexpr,
+ coerceDefaultJsonExpr(pstate, jsexpr, false,
jsexpr->on_error->default_expr);
jsexpr->wrapper = func->wrapper;
@@ -4409,6 +4376,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 +4384,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 +4414,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..43a96fc5142 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -108,10 +108,9 @@ anytime_typmodout(bool istz, int32 typmod)
/* date_in()
* Given date text string, convert to internal date format.
*/
-Datum
-date_in(PG_FUNCTION_ARGS)
+DateADT
+date_in_opt_error(char *str, bool *error)
{
- char *str = PG_GETARG_CSTRING(0);
DateADT date;
fsec_t fsec;
struct pg_tm tt,
@@ -127,9 +126,18 @@ date_in(PG_FUNCTION_ARGS)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp);
+ dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp,
+ !error);
if (dterr != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
DateTimeParseError(dterr, str, "date");
+ }
switch (dtype)
{
@@ -149,25 +157,56 @@ date_in(PG_FUNCTION_ARGS)
PG_RETURN_DATEADT(date);
default:
+ if (*error)
+ {
+ *error = true;
+ return 0;
+ }
+
DateTimeParseError(DTERR_BAD_FORMAT, str, "date");
break;
}
/* Prevent overflow in Julian-day routines */
if (!IS_VALID_JULIAN(tm->tm_year, tm->tm_mon, tm->tm_mday))
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("date out of range: \"%s\"", str)));
+ }
date = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
/* Now check for just-out-of-range dates */
if (!IS_VALID_DATE(date))
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("date out of range: \"%s\"", str)));
+ }
- PG_RETURN_DATEADT(date);
+ return date;
+}
+
+/* date_in()
+ * Given date text string, convert to internal date format.
+ */
+Datum
+date_in(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_DATEADT(date_in_opt_error(PG_GETARG_CSTRING(0), NULL));
}
/* date_out()
@@ -1286,10 +1325,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 +1340,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 +1381,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 +1397,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));
}
@@ -1363,15 +1429,9 @@ timestamptz_date(PG_FUNCTION_ARGS)
* Time ADT
*****************************************************************************/
-Datum
-time_in(PG_FUNCTION_ARGS)
+TimeADT
+time_in_opt_error(char *str, int32 typmod, bool *error)
{
- char *str = PG_GETARG_CSTRING(0);
-
-#ifdef NOT_USED
- Oid typelem = PG_GETARG_OID(1);
-#endif
- int32 typmod = PG_GETARG_INT32(2);
TimeADT result;
fsec_t fsec;
struct pg_tm tt,
@@ -1387,14 +1447,35 @@ time_in(PG_FUNCTION_ARGS)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz);
+ dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz,
+ !error);
if (dterr != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
DateTimeParseError(dterr, str, "time");
+ }
tm2time(tm, fsec, &result);
AdjustTimeForTypmod(&result, typmod);
- PG_RETURN_TIMEADT(result);
+ return result;
+}
+
+Datum
+time_in(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
+#ifdef NOT_USED
+ Oid typelem = PG_GETARG_OID(1);
+#endif
+ int32 typmod = PG_GETARG_INT32(2);
+
+ PG_RETURN_TIMEADT(time_in_opt_error(str, typmod, NULL));
}
/* tm2time()
@@ -1889,22 +1970,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 +2003,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 +2037,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 +2061,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()
@@ -2249,15 +2384,9 @@ tm2timetz(struct pg_tm *tm, fsec_t fsec, int tz, TimeTzADT *result)
return 0;
}
-Datum
-timetz_in(PG_FUNCTION_ARGS)
+TimeTzADT *
+timetz_in_opt_error(char *str, int32 typmod, bool *error)
{
- char *str = PG_GETARG_CSTRING(0);
-
-#ifdef NOT_USED
- Oid typelem = PG_GETARG_OID(1);
-#endif
- int32 typmod = PG_GETARG_INT32(2);
TimeTzADT *result;
fsec_t fsec;
struct pg_tm tt,
@@ -2273,17 +2402,38 @@ timetz_in(PG_FUNCTION_ARGS)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz);
+ dterr = DecodeTimeOnly(field, ftype, nf, &dtype, tm, &fsec, &tz,
+ !error);
if (dterr != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return NULL;
+ }
+
DateTimeParseError(dterr, str, "time with time zone");
+ }
result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
tm2timetz(tm, fsec, tz, result);
AdjustTimeForTypmod(&(result->time), typmod);
- PG_RETURN_TIMETZADT_P(result);
+ return result;
}
+Datum
+timetz_in(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
+
+#ifdef NOT_USED
+ Oid typelem = PG_GETARG_OID(1);
+#endif
+ int32 typmod = PG_GETARG_INT32(2);
+
+ PG_RETURN_TIMETZADT_P(timetz_in_opt_error(str, typmod, NULL));
+}
Datum
timetz_out(PG_FUNCTION_ARGS)
{
@@ -2812,10 +2962,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 +2972,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/datetime.c b/src/backend/utils/adt/datetime.c
index 43fff50d490..3275e5fd948 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -971,7 +971,8 @@ ParseDateTime(const char *timestr, char *workbuf, size_t buflen,
*/
int
DecodeDateTime(char **field, int *ftype, int nf,
- int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp)
+ int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp,
+ bool throw_errors)
{
int fmask = 0,
tmask,
@@ -1114,8 +1115,13 @@ DecodeDateTime(char **field, int *ftype, int nf,
/*
* We should return an error code instead of
* ereport'ing directly, but then there is no way
- * to report the bad time zone name.
+ * to report the bad time zone name. But
+ * throwing errors is disallowed, simply
+ * return error code.
*/
+ if (!throw_errors)
+ return DTERR_BAD_FORMAT;
+
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("time zone \"%s\" not recognized",
@@ -1921,7 +1927,8 @@ DetermineTimeZoneAbbrevOffsetInternal(pg_time_t t, const char *abbr, pg_tz *tzp,
*/
int
DecodeTimeOnly(char **field, int *ftype, int nf,
- int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp)
+ int *dtype, struct pg_tm *tm, fsec_t *fsec, int *tzp,
+ bool throw_errors)
{
int fmask = 0,
tmask,
@@ -2022,6 +2029,9 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
* ereport'ing directly, but then there is no way
* to report the bad time zone name.
*/
+ if (!throw_errors)
+ return DTERR_BAD_FORMAT;
+
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("time zone \"%s\" not recognized",
diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c
index fc8f39a7a98..d9da8c8466d 100644
--- a/src/backend/utils/adt/float.c
+++ b/src/backend/utils/adt/float.c
@@ -73,6 +73,16 @@ static double sind_q1(double x);
static double cosd_q1(double x);
static void init_degree_constants(void);
+/* Convenience macro: set *have_error flag (if provided) or throw error */
+#define RETURN_ERROR(throw_error, have_error) \
+do { \
+ if (have_error) { \
+ *have_error = true; \
+ return 0.0; \
+ } else { \
+ throw_error; \
+ } \
+} while (0)
/*
* We use these out-of-line ereport() calls to report float overflow,
@@ -159,10 +169,9 @@ is_infinite(double val)
* result of 0xAE43FEp-107.
*
*/
-Datum
-float4in(PG_FUNCTION_ARGS)
+float
+float4in_opt_error(char *num, bool *have_error)
{
- char *num = PG_GETARG_CSTRING(0);
char *orig_num;
float val;
char *endptr;
@@ -183,10 +192,11 @@ float4in(PG_FUNCTION_ARGS)
* strtod() on different platforms.
*/
if (*num == '\0')
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "real", orig_num)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "real", orig_num))),
+ have_error);
errno = 0;
val = strtof(num, &endptr);
@@ -257,16 +267,18 @@ float4in(PG_FUNCTION_ARGS)
(val >= HUGE_VALF || val <= -HUGE_VALF)
#endif
)
- ereport(ERROR,
- (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
- errmsg("\"%s\" is out of range for type real",
- orig_num)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("\"%s\" is out of range for type real",
+ orig_num))),
+ have_error);
}
else
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "real", orig_num)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "real", orig_num))),
+ have_error);
}
/* skip trailing whitespace */
@@ -275,12 +287,19 @@ float4in(PG_FUNCTION_ARGS)
/* if there is any junk left at the end of the string, bail out */
if (*endptr != '\0')
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "real", orig_num)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "real", orig_num))),
+ have_error);
+
+ return val;
+}
- PG_RETURN_FLOAT4(val);
+Datum
+float4in(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_FLOAT4(float4in_opt_error(PG_GETARG_CSTRING(0), NULL));
}
/*
@@ -340,17 +359,6 @@ float8in(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8(float8in_internal(num, NULL, "double precision", num));
}
-/* Convenience macro: set *have_error flag (if provided) or throw error */
-#define RETURN_ERROR(throw_error, have_error) \
-do { \
- if (have_error) { \
- *have_error = true; \
- return 0.0; \
- } else { \
- throw_error; \
- } \
-} while (0)
-
/*
* float8in_internal_opt_error - guts of float8in()
*
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/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index da9df4ae766..9c9f9ad703f 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -1318,9 +1318,11 @@ jspIsMutableWalker(JsonPathItem *jpi, JsonPathMutableContext *cxt)
* Check whether jsonpath expression is immutable or not.
*/
bool
-jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
+jspIsMutable(JsonPath *path, List *varnames, List *varexprs,
+ bool *returns_datetime)
{
JsonPathMutableContext cxt;
+ JsonPathDatatypeStatus status;
JsonPathItem jpi;
cxt.varnames = varnames;
@@ -1330,7 +1332,9 @@ jspIsMutable(JsonPath *path, List *varnames, List *varexprs)
cxt.mutable = false;
jspInit(&jpi, path);
- jspIsMutableWalker(&jpi, &cxt);
+ status = jspIsMutableWalker(&jpi, &cxt);
+
+ *returns_datetime = status != jpdsNonDateTime;
return cxt.mutable;
}
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 920a63b0081..951b2f43ab1 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -498,7 +498,7 @@ static void free_var(NumericVar *var);
static void zero_var(NumericVar *var);
static const char *set_var_from_str(const char *str, const char *cp,
- NumericVar *dest);
+ NumericVar *dest, bool *have_error);
static void set_var_from_num(Numeric value, NumericVar *dest);
static void init_var_from_num(Numeric num, NumericVar *dest);
static void set_var_from_var(const NumericVar *value, NumericVar *dest);
@@ -512,8 +512,8 @@ static Numeric duplicate_numeric(Numeric num);
static Numeric make_result(const NumericVar *var);
static Numeric make_result_opt_error(const NumericVar *var, bool *error);
-static void apply_typmod(NumericVar *var, int32 typmod);
-static void apply_typmod_special(Numeric num, int32 typmod);
+static bool apply_typmod(NumericVar *var, int32 typmod, bool *error);
+static void apply_typmod_special(Numeric num, int32 typmod, bool *error);
static bool numericvar_to_int32(const NumericVar *var, int32 *result);
static bool numericvar_to_int64(const NumericVar *var, int64 *result);
@@ -607,21 +607,25 @@ static void accum_sum_combine(NumericSumAccum *accum, NumericSumAccum *accum2);
* ----------------------------------------------------------------------
*/
+/* Convenience macro: set *have_error flag (if provided) or throw error */
+#define RETURN_ERROR(throw_error, have_error) \
+do { \
+ if (have_error) { \
+ *(have_error) = true; \
+ return NULL; \
+ } else { \
+ throw_error; \
+ } \
+} while (0)
/*
* numeric_in() -
*
* Input function for numeric data type
*/
-Datum
-numeric_in(PG_FUNCTION_ARGS)
+Numeric
+numeric_in_opt_error(char *str, int32 typmod, bool *have_error)
{
- char *str = PG_GETARG_CSTRING(0);
-
-#ifdef NOT_USED
- Oid typelem = PG_GETARG_OID(1);
-#endif
- int32 typmod = PG_GETARG_INT32(2);
Numeric res;
const char *cp;
@@ -682,7 +686,10 @@ numeric_in(PG_FUNCTION_ARGS)
init_var(&value);
- cp = set_var_from_str(str, cp, &value);
+ cp = set_var_from_str(str, cp, &value, have_error);
+
+ if (!cp)
+ return NULL; /* error */
/*
* We duplicate a few lines of code here because we would like to
@@ -693,38 +700,59 @@ numeric_in(PG_FUNCTION_ARGS)
while (*cp)
{
if (!isspace((unsigned char) *cp))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "numeric", str)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "numeric", str))),
+ have_error);
cp++;
}
- apply_typmod(&value, typmod);
+ if (!apply_typmod(&value, typmod, have_error))
+ return NULL; /* error */
- res = make_result(&value);
+ res = make_result_opt_error(&value, have_error);
free_var(&value);
- PG_RETURN_NUMERIC(res);
+ return res;
}
/* Should be nothing left but spaces */
while (*cp)
{
if (!isspace((unsigned char) *cp))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "numeric", str)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "numeric", str))),
+ have_error);
+
cp++;
}
/* As above, throw any typmod error after finishing syntax check */
- apply_typmod_special(res, typmod);
+ apply_typmod_special(res, typmod, have_error);
- PG_RETURN_NUMERIC(res);
+ return res;
}
+/*
+ * numeric_in() -
+ *
+ * Input function for numeric data type
+ */
+Datum
+numeric_in(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
+
+#ifdef NOT_USED
+ Oid typelem = PG_GETARG_OID(1);
+#endif
+ int32 typmod = PG_GETARG_INT32(2);
+
+ PG_RETURN_NUMERIC(numeric_in_opt_error(str, typmod, NULL));
+}
/*
* numeric_out() -
@@ -1058,7 +1086,7 @@ numeric_recv(PG_FUNCTION_ARGS)
{
trunc_var(&value, value.dscale);
- apply_typmod(&value, typmod);
+ apply_typmod(&value, typmod, NULL);
res = make_result(&value);
}
@@ -1067,7 +1095,7 @@ numeric_recv(PG_FUNCTION_ARGS)
/* apply_typmod_special wants us to make the Numeric first */
res = make_result(&value);
- apply_typmod_special(res, typmod);
+ apply_typmod_special(res, typmod, NULL);
}
free_var(&value);
@@ -1180,7 +1208,7 @@ numeric (PG_FUNCTION_ARGS)
*/
if (NUMERIC_IS_SPECIAL(num))
{
- apply_typmod_special(num, typmod);
+ apply_typmod_special(num, typmod, NULL);
PG_RETURN_NUMERIC(duplicate_numeric(num));
}
@@ -1231,7 +1259,7 @@ numeric (PG_FUNCTION_ARGS)
init_var(&var);
set_var_from_num(num, &var);
- apply_typmod(&var, typmod);
+ apply_typmod(&var, typmod, NULL);
new = make_result(&var);
free_var(&var);
@@ -4308,15 +4336,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 +4364,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 +4393,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 +4421,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)
@@ -4412,7 +4480,7 @@ float8_numeric(PG_FUNCTION_ARGS)
init_var(&result);
/* Assume we need not worry about leading/trailing spaces */
- (void) set_var_from_str(buf, buf, &result);
+ (void) set_var_from_str(buf, buf, &result, NULL);
res = make_result(&result);
@@ -4505,7 +4573,7 @@ float4_numeric(PG_FUNCTION_ARGS)
init_var(&result);
/* Assume we need not worry about leading/trailing spaces */
- (void) set_var_from_str(buf, buf, &result);
+ (void) set_var_from_str(buf, buf, &result, NULL);
res = make_result(&result);
@@ -6811,7 +6879,8 @@ zero_var(NumericVar *var)
* reports. (Typically cp would be the same except advanced over spaces.)
*/
static const char *
-set_var_from_str(const char *str, const char *cp, NumericVar *dest)
+set_var_from_str(const char *str, const char *cp, NumericVar *dest,
+ bool *have_error)
{
bool have_dp = false;
int i;
@@ -6849,10 +6918,11 @@ set_var_from_str(const char *str, const char *cp, NumericVar *dest)
}
if (!isdigit((unsigned char) *cp))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "numeric", str)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "numeric", str))),
+ have_error);
decdigits = (unsigned char *) palloc(strlen(cp) + DEC_DIGITS * 2);
@@ -6873,10 +6943,11 @@ set_var_from_str(const char *str, const char *cp, NumericVar *dest)
else if (*cp == '.')
{
if (have_dp)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "numeric", str)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "numeric", str))),
+ have_error);
have_dp = true;
cp++;
}
@@ -6897,10 +6968,11 @@ set_var_from_str(const char *str, const char *cp, NumericVar *dest)
cp++;
exponent = strtol(cp, &endptr, 10);
if (endptr == cp)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("invalid input syntax for type %s: \"%s\"",
- "numeric", str)));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "numeric", str))),
+ have_error);
cp = endptr;
/*
@@ -6912,9 +6984,10 @@ set_var_from_str(const char *str, const char *cp, NumericVar *dest)
* for consistency use the same ereport errcode/text as make_result().
*/
if (exponent >= INT_MAX / 2 || exponent <= -(INT_MAX / 2))
- ereport(ERROR,
- (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
- errmsg("value overflows numeric format")));
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("value overflows numeric format"))),
+ have_error);
dweight += (int) exponent;
dscale -= (int) exponent;
if (dscale < 0)
@@ -7420,17 +7493,10 @@ make_result_opt_error(const NumericVar *var, bool *have_error)
if (NUMERIC_WEIGHT(result) != weight ||
NUMERIC_DSCALE(result) != var->dscale)
{
- if (have_error)
- {
- *have_error = true;
- return NULL;
- }
- else
- {
- ereport(ERROR,
- (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
- errmsg("value overflows numeric format")));
- }
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+ errmsg("value overflows numeric format"))),
+ have_error);
}
dump_numeric("make_result()", result);
@@ -7456,8 +7522,8 @@ make_result(const NumericVar *var)
* Do bounds checking and rounding according to the specified typmod.
* Note that this is only applied to normal finite values.
*/
-static void
-apply_typmod(NumericVar *var, int32 typmod)
+static bool
+apply_typmod(NumericVar *var, int32 typmod, bool *have_error)
{
int precision;
int scale;
@@ -7467,7 +7533,7 @@ apply_typmod(NumericVar *var, int32 typmod)
/* Do nothing if we have an invalid typmod */
if (!is_valid_numeric_typmod(typmod))
- return;
+ return true;
precision = numeric_typmod_precision(typmod);
scale = numeric_typmod_scale(typmod);
@@ -7514,6 +7580,13 @@ apply_typmod(NumericVar *var, int32 typmod)
#error unsupported NBASE
#endif
if (ddigits > maxdigits)
+ {
+ if (have_error)
+ {
+ *have_error = true;
+ return false;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("numeric field overflow"),
@@ -7523,11 +7596,14 @@ apply_typmod(NumericVar *var, int32 typmod)
maxdigits ? "10^" : "",
maxdigits ? maxdigits : 1
)));
+ }
break;
}
ddigits -= DEC_DIGITS;
}
}
+
+ return true;
}
/*
@@ -7537,7 +7613,7 @@ apply_typmod(NumericVar *var, int32 typmod)
* For convenience of most callers, the value is presented in packed form.
*/
static void
-apply_typmod_special(Numeric num, int32 typmod)
+apply_typmod_special(Numeric num, int32 typmod, bool *have_error)
{
int precision;
int scale;
@@ -7560,6 +7636,12 @@ apply_typmod_special(Numeric num, int32 typmod)
precision = numeric_typmod_precision(typmod);
scale = numeric_typmod_scale(typmod);
+ if (have_error)
+ {
+ *have_error = true;
+ return;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("numeric field overflow"),
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..04a0e3eb063 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -139,18 +139,12 @@ anytimestamp_typmodout(bool istz, int32 typmod)
* USER I/O ROUTINES *
*****************************************************************************/
-/* timestamp_in()
- * Convert a string to internal form.
+/* timestamp_in_opt_error()
+ * Convert a string to internal form, returning error instead of throwing if needed.
*/
-Datum
-timestamp_in(PG_FUNCTION_ARGS)
+Timestamp
+timestamp_in_opt_error(char *str, int32 typmod, bool *error)
{
- char *str = PG_GETARG_CSTRING(0);
-
-#ifdef NOT_USED
- Oid typelem = PG_GETARG_OID(1);
-#endif
- int32 typmod = PG_GETARG_INT32(2);
Timestamp result;
fsec_t fsec;
struct pg_tm tt,
@@ -166,17 +160,34 @@ timestamp_in(PG_FUNCTION_ARGS)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
+ dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz,
+ !error);
if (dterr != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
DateTimeParseError(dterr, str, "timestamp");
+ }
switch (dtype)
{
case DTK_DATE:
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: \"%s\"", str)));
+ }
break;
case DTK_EPOCH:
@@ -197,11 +208,27 @@ timestamp_in(PG_FUNCTION_ARGS)
TIMESTAMP_NOEND(result);
}
- AdjustTimestampForTypmod(&result, typmod);
+ AdjustTimestampForTypmodError(&result, typmod, error);
PG_RETURN_TIMESTAMP(result);
}
+/* timestamp_in()
+ * Convert a string to internal form.
+ */
+Datum
+timestamp_in(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
+
+#ifdef NOT_USED
+ Oid typelem = PG_GETARG_OID(1);
+#endif
+ int32 typmod = PG_GETARG_INT32(2);
+
+ PG_RETURN_TIMESTAMP(timestamp_in_opt_error(str, typmod, NULL));
+}
+
/* timestamp_out()
* Convert a timestamp to external form.
*/
@@ -400,15 +427,9 @@ AdjustTimestampForTypmod(Timestamp *time, int32 typmod)
/* timestamptz_in()
* Convert a string to internal form.
*/
-Datum
-timestamptz_in(PG_FUNCTION_ARGS)
+TimestampTz
+timestamptz_in_opt_error(char *str, int32 typmod, bool *error)
{
- char *str = PG_GETARG_CSTRING(0);
-
-#ifdef NOT_USED
- Oid typelem = PG_GETARG_OID(1);
-#endif
- int32 typmod = PG_GETARG_INT32(2);
TimestampTz result;
fsec_t fsec;
struct pg_tm tt,
@@ -424,17 +445,34 @@ timestamptz_in(PG_FUNCTION_ARGS)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
+ dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz,
+ !error);
if (dterr != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
DateTimeParseError(dterr, str, "timestamp with time zone");
+ }
switch (dtype)
{
case DTK_DATE:
if (tm2timestamp(tm, fsec, &tz, &result) != 0)
+ {
+ if (error)
+ {
+ *error = true;
+ return 0;
+ }
+
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range: \"%s\"", str)));
+ }
break;
case DTK_EPOCH:
@@ -455,9 +493,24 @@ timestamptz_in(PG_FUNCTION_ARGS)
TIMESTAMP_NOEND(result);
}
- AdjustTimestampForTypmod(&result, typmod);
+ AdjustTimestampForTypmodError(&result, typmod, error);
- PG_RETURN_TIMESTAMPTZ(result);
+ return result;
+}
+
+/* timestamptz_in()
+ * Convert a string to internal form.
+ */
+Datum
+timestamptz_in(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
+#ifdef NOT_USED
+ Oid typelem = PG_GETARG_OID(1);
+#endif
+ int32 typmod = PG_GETARG_INT32(2);
+
+ PG_RETURN_TIMESTAMPTZ(timestamptz_in_opt_error(str, typmod, NULL));
}
/*
@@ -5614,8 +5667,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 +5681,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/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 9fbbfb1be54..7877bce6043 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -12866,7 +12866,7 @@ check_recovery_target_time(char **newval, void **extra, GucSource source)
dterr = ParseDateTime(str, workbuf, sizeof(workbuf),
field, ftype, MAXDATEFIELDS, &nf);
if (dterr == 0)
- dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz);
+ dterr = DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tz, true);
if (dterr != 0)
return false;
if (dtype != DTK_DATE)
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/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 6c5203dc448..4d61b88c161 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -55,4 +55,6 @@ extern Query *inline_set_returning_function(PlannerInfo *root,
extern Bitmapset *pull_paramids(Expr *expr);
+extern bool expr_can_throw_errors(Node *expr);
+
#endif /* CLAUSES_H */
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..f29168a025b 100644
--- a/src/include/utils/date.h
+++ b/src/include/utils/date.h
@@ -87,4 +87,17 @@ 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);
+extern DateADT date_in_opt_error(char *str, bool *error);
+extern TimeADT time_in_opt_error(char *str, int32 typmod, bool *error);
+extern TimeTzADT *timetz_in_opt_error(char *str, int32 typmod, bool *error);
+
#endif /* DATE_H */
diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h
index 4527e825177..68241d7c4cc 100644
--- a/src/include/utils/datetime.h
+++ b/src/include/utils/datetime.h
@@ -295,11 +295,13 @@ extern int ParseDateTime(const char *timestr, char *workbuf, size_t buflen,
int maxfields, int *numfields);
extern int DecodeDateTime(char **field, int *ftype,
int nf, int *dtype,
- struct pg_tm *tm, fsec_t *fsec, int *tzp);
+ struct pg_tm *tm, fsec_t *fsec, int *tzp,
+ bool throw_errors);
extern int DecodeTimezone(char *str, int *tzp);
extern int DecodeTimeOnly(char **field, int *ftype,
int nf, int *dtype,
- struct pg_tm *tm, fsec_t *fsec, int *tzp);
+ struct pg_tm *tm, fsec_t *fsec, int *tzp,
+ bool throw_errors);
extern int DecodeInterval(char **field, int *ftype, int nf, int range,
int *dtype, struct pg_itm_in *itm_in);
extern int DecodeISO8601Interval(char *str,
diff --git a/src/include/utils/float.h b/src/include/utils/float.h
index 4bf0e3ac07a..0ad4d9f3973 100644
--- a/src/include/utils/float.h
+++ b/src/include/utils/float.h
@@ -46,6 +46,7 @@ extern float8 float8in_internal(char *num, char **endptr_p,
extern float8 float8in_internal_opt_error(char *num, char **endptr_p,
const char *type_name, const char *orig_string,
bool *have_error);
+extern float float4in_opt_error(char *num, bool *have_error);
extern char *float8out_internal(float8 num);
extern int float4_cmp_internal(float4 a, float4 b);
extern int float8_cmp_internal(float8 a, float8 b);
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/jsonpath.h b/src/include/utils/jsonpath.h
index 8e79b8dc9f0..e9134b7eab4 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -177,7 +177,8 @@ extern bool jspGetBool(JsonPathItem *v);
extern char *jspGetString(JsonPathItem *v, int32 *len);
extern bool jspGetArraySubscript(JsonPathItem *v, JsonPathItem *from,
JsonPathItem *to, int i);
-extern bool jspIsMutable(JsonPath *path, List *varnames, List *varexprs);
+extern bool jspIsMutable(JsonPath *path, List *varnames, List *varexprs,
+ bool *returns_datetime);
extern const char *jspOperationName(JsonPathItemType type);
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
index 3caa74dfe7a..f32807cfdaa 100644
--- a/src/include/utils/numeric.h
+++ b/src/include/utils/numeric.h
@@ -85,6 +85,10 @@ 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);
+extern Numeric numeric_in_opt_error(char *str, int32 typmod,
+ bool *have_error);
#endif /* _PG_NUMERIC_H_ */
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index edf3a973186..2685d14342d 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -102,8 +102,14 @@ 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);
+extern Timestamp timestamp_in_opt_error(char *str, int32 typmod,
+ bool *error);
+extern TimestampTz timestamptz_in_opt_error(char *str, int32 typmod,
+ bool *error);
extern int isoweek2j(int year, int week);
extern void isoweek2date(int woy, int *year, int *mon, int *mday);
diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out
index ef496110af3..2982c191a80 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,129 @@ SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
03-01-2017
(1 row)
--- Test NULL checks execution in domain types
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float4);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float4);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float4);
+ json_value
+------------
+ NaN
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float4);
+ json_value
+------------
+ -Infinity
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 DEFAULT -1 ON ERROR);
+ json_value
+------------
+ -1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 ERROR ON ERROR);
+ERROR: invalid input syntax for type real: "err"
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float8);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float8);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float8);
+ json_value
+------------
+ NaN
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float8);
+ json_value
+------------
+ -Infinity
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 DEFAULT -1 ON ERROR);
+ json_value
+------------
+ -1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 ERROR ON ERROR);
+ERROR: invalid input syntax for type double precision: "err"
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING numeric);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
+ json_value
+------------
+ 1.23
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING numeric);
+ json_value
+------------
+ NaN
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING numeric);
+ json_value
+------------
+ -Infinity
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric DEFAULT -1 ON ERROR);
+ json_value
+------------
+ -1
+(1 row)
+
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric ERROR ON ERROR);
+ERROR: invalid input syntax for type numeric: "err"
+-- 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_...
+ ^
+-- 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 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...
+ ^
+-- Test returning of non-scalar items
SELECT JSON_VALUE(jsonb '[]', '$');
json_value
------------
@@ -478,11 +595,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
@@ -514,6 +629,36 @@ SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +
"2018-02-21T02:34:56+00:00"
(1 row)
+-- test errors in DEFAULT ON EMPTY expressions
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int
+ DEFAULT 1 / x ON EMPTY
+ DEFAULT 2 ON ERROR)
+FROM (VALUES (1::int), (0)) x(x);
+ERROR: unsafe DEFAULT ON EMPTY expressions are not supported
+LINE 2: DEFAULT 1 / x ON EMPTY
+ ^
+HINT: Use ERROR ON ERROR clause or try to simplify expression into constant-like form
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPTY);
+ json_value
+------------
+ -1
+(1 row)
+
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EMPTY);
+ json_value
+------------
+ -1
+(1 row)
+
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON EMPTY);
+ERROR: invalid input syntax for type integer: "err"
+LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON E...
+ ^
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ON EMPTY);
+ERROR: unsafe DEFAULT ON EMPTY expressions are not supported
+LINE 1: ...SON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ...
+ ^
+HINT: Use ERROR ON ERROR clause or try to simplify expression into constant-like form
-- JSON_QUERY
SELECT
JSON_QUERY(js, '$'),
@@ -852,49 +997,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
@@ -1019,6 +1150,25 @@ ERROR: functions in index expression must be marked IMMUTABLE
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
ERROR: functions in index expression must be marked IMMUTABLE
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int)); -- immutable
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING time));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timetz));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamp));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamptz));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()[*][1,2].**'));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$x' PASSING '2022-08-25'::date AS x));
+ERROR: functions in index expression must be marked IMMUTABLE
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()' RETURNING int)); -- immutable
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()')); -- immutable
DROP TABLE test_jsonb_mutability;
-- JSON_TABLE
-- Should fail (JSON_TABLE can be used only in FROM clause)
@@ -1048,7 +1198,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 +1218,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,29 +1233,26 @@ 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;
- js | id | id2 | int | text | char(4) | bool | numeric | domain | js | jb | jst | jsc | jsv | jsb | jsbq | aaa | aaa1 | exists1 | exists2 | exists3 | exists4 | js2 | jsb2w | jsb2q | ia | ta | jba
----------------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+---------+--------------+--------------+--------------+------+------+--------------+--------------+-----+------+---------+---------+---------+---------+--------------+----------------+--------------+----+----+-----
- 1 | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1 | | |
- [] | | | | | | | | | | | | | | | | | | | | | | | | | | |
- {} | 1 | 1 | | | | | | | {} | {} | {} | {} | {} | {} | {} | | | f | 0 | | false | {} | [{}] | {} | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1 | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 2 | 1 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | | | f | 0 | | false | 1.23 | [1.23] | 1.23 | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 3 | 2 | 2 | 2 | | 2 | 2 | "2" | "2" | "2" | "2" | "2" | "2" | 2 | | | f | 0 | | false | "2" | ["2"] | 2 | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | 4 | | aaaaaaa | aaaa | | | aaaaaaa | "aaaaaaa" | "aaaaaaa" | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | | | | f | 0 | | false | "aaaaaaa" | ["aaaaaaa"] | | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | 5 | | foo | foo | | | | "foo" | "foo" | "foo" | "foo | "foo | "foo" | | | | f | 0 | | false | "foo" | ["foo"] | | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | 6 | | | | | | | null | null | null | null | null | null | null | | | f | 0 | | false | null | [null] | null | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | 7 | 0 | false | fals | f | | false | false | false | false | fals | fals | false | false | | | f | 0 | | false | false | [false] | false | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | 8 | 1 | true | true | t | | true | true | true | true | true | true | true | true | | | f | 0 | | false | true | [true] | true | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 9 | | | | | | | {"aaa": 123} | {"aaa": 123} | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | {"aaa": 123} | 123 | 123 | t | 1 | 1 | true | {"aaa": 123} | [{"aaa": 123}] | {"aaa": 123} | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | 10 | | [1,2] | [1,2 | | | [1,2] | "[1,2]" | "[1,2]" | "[1,2]" | "[1, | "[1, | "[1,2]" | [1, 2] | | | f | 0 | | false | "[1,2]" | ["[1,2]"] | [1, 2] | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | 11 | | "str" | "str | | | "str" | "\"str\"" | "\"str\"" | "\"str\"" | "\"s | "\"s | "\"str\"" | "str" | | | f | 0 | | false | "\"str\"" | ["\"str\""] | "str" | | |
+ js | id | id2 | int | text | char(4) | bool | numeric | js | jb | jst | jsc | jsv | jsb | jsbq | aaa | aaa1 | exists1 | exists2 | exists3 | exists4 | js2 | jsb2w | jsb2q
+---------------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+--------------+--------------+--------------+------+------+--------------+--------------+-----+------+---------+---------+---------+---------+--------------+----------------+--------------
+ 1 | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1
+ [] | | | | | | | | | | | | | | | | | | | | | | |
+ {} | 1 | 1 | | | | | | {} | {} | {} | {} | {} | {} | {} | | | f | 0 | | false | {} | [{}] | {}
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 2 | 1 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | | | f | 0 | | false | 1.23 | [1.23] | 1.23
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 3 | 2 | 2 | 2 | | 2 | "2" | "2" | "2" | "2" | "2" | "2" | 2 | | | f | 0 | | false | "2" | ["2"] | 2
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | 4 | | aaaaaaa | aaaa | | | "aaaaaaa" | "aaaaaaa" | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | | | | f | 0 | | false | "aaaaaaa" | ["aaaaaaa"] |
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | 5 | | foo | foo | | | "foo" | "foo" | "foo" | "foo | "foo | "foo" | | | | f | 0 | | false | "foo" | ["foo"] |
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | 6 | | | | | | null | null | null | null | null | null | null | | | f | 0 | | false | null | [null] | null
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | 7 | 0 | false | fals | f | | false | false | false | fals | fals | false | false | | | f | 0 | | false | false | [false] | false
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | 8 | 1 | true | true | t | | true | true | true | true | true | true | true | | | f | 0 | | false | true | [true] | true
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 9 | | | | | | {"aaa": 123} | {"aaa": 123} | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | {"aaa": 123} | 123 | 123 | t | 1 | 1 | true | {"aaa": 123} | [{"aaa": 123}] | {"aaa": 123}
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | 10 | | [1,2] | [1,2 | | | "[1,2]" | "[1,2]" | "[1,2]" | "[1, | "[1, | "[1,2]" | [1, 2] | | | f | 0 | | false | "[1,2]" | ["[1,2]"] | [1, 2]
+ [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | 11 | | "str" | "str | | | "\"str\"" | "\"str\"" | "\"str\"" | "\"s | "\"s | "\"str\"" | "str" | | | f | 0 | | false | "\"str\"" | ["\"str\""] | "str"
(14 rows)
-- JSON_TABLE: Test backward parsing
@@ -1123,7 +1268,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 +1283,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 +1309,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 +1324,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 +1342,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 +1357,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 +1381,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 +1450,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 +1468,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 +2234,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..43a194ad09f 100644
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -82,12 +82,43 @@ 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
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float4);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 DEFAULT -1 ON ERROR);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float4 ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING float8);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 DEFAULT -1 ON ERROR);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING float8 ERROR ON ERROR);
+
+SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"nan"', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"-inf"', '$' RETURNING numeric);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric DEFAULT -1 ON ERROR);
+SELECT JSON_VALUE(jsonb '"err"', '$' RETURNING numeric ERROR ON ERROR);
+
+-- 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);
+
+-- 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 NULL ON ERROR);
SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
+
+-- Test returning of non-scalar items
SELECT JSON_VALUE(jsonb '[]', '$');
SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
SELECT JSON_VALUE(jsonb '{}', '$');
@@ -133,6 +164,17 @@ SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +
SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
+-- test errors in DEFAULT ON EMPTY expressions
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int
+ DEFAULT 1 / x ON EMPTY
+ DEFAULT 2 ON ERROR)
+FROM (VALUES (1::int), (0)) x(x);
+
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1 ON EMPTY);
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT '-1' ON EMPTY);
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT 'err' ON EMPTY);
+SELECT JSON_VALUE('1'::jsonb, '$.x' RETURNING int DEFAULT -1::float ON EMPTY);
+
-- JSON_QUERY
SELECT
@@ -318,6 +360,21 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("YY-MM-DD") ?
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
+
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING int)); -- immutable
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING date));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING time));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timetz));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamp));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' RETURNING timestamptz));
+
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()[*][1,2].**'));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$x' PASSING '2022-08-25'::date AS x));
+CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$.datetime()' RETURNING int)); -- immutable
+
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()')); -- immutable
+
DROP TABLE test_jsonb_mutability;
-- JSON_TABLE
@@ -338,8 +395,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 +415,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 +431,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 +450,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 +466,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 +490,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
view thread (95+ messages) latest in thread
Message-ID: <6ba7ba7b-9fdd-674f-c227-59a59e0de1ec@postgrespro.ru>
Permalink: ../6ba7ba7b-9fdd-674f-c227-59a59e0de1ec@postgrespro.ru/
Also on: postgresql.org/message-id/6ba7ba7b-9fdd-674f-c227-59a59e0de1ec@postgrespro.ru
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: n.gluhov@postgrespro.ru, andrew@dunslane.net, jkatz@postgresql.org, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, andres@anarazel.de, pgsql-hackers@lists.postgresql.org, michael@paquier.xyz, john.naylor@enterprisedb.com, amitlangote09@gmail.com
Subject: Re: SQL/JSON features for v15
In-Reply-To: <6ba7ba7b-9fdd-674f-c227-59a59e0de1ec@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox