From cb5d94295986298af666534970f9bb3715574646 Mon Sep 17 00:00:00 2001 From: Teodor Sigaev Date: Wed, 9 May 2018 13:23:16 +0300 Subject: [PATCH] Improve jsonb cast error message Initial variant of error message didn't follow style of another casting error messages and wasn't informative. Per gripe from Robert Haas. Reviewer: Tom Lane Discussion: https://www.postgresql.org/message-id/flat/CA%2BTgmob08StTV9yu04D0idRFNMh%2BUoyKax5Otvrix7rEZC8rMw%40mail.gmail.com#CA+Tgmob08StTV9yu04D0idRFNMh+UoyKax5Otvrix7rEZC8rMw@mail.gmail.com --- src/backend/utils/adt/jsonb.c | 71 +++++++++++++++++++---------- src/test/regress/expected/jsonb.out | 8 ++-- 2 files changed, 51 insertions(+), 28 deletions(-) diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index 9d2b89f90c..6940b11c29 100644 --- a/src/backend/utils/adt/jsonb.c +++ b/src/backend/utils/adt/jsonb.c @@ -1857,7 +1857,7 @@ jsonb_object_agg_finalfn(PG_FUNCTION_ARGS) /* * Extract scalar value from raw-scalar pseudo-array jsonb. */ -static JsonbValue * +static bool JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res) { JsonbIterator *it; @@ -1865,7 +1865,11 @@ JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res) JsonbValue tmp; if (!JsonContainerIsArray(jbc) || !JsonContainerIsScalar(jbc)) - return NULL; + { + /* inform caller about actual type of container */ + res->type = (JsonContainerIsArray(jbc)) ? jbvArray : jbvObject; + return false; + } /* * A root scalar is stored as an array of one element, so we get the array @@ -1887,7 +1891,40 @@ JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res) tok = JsonbIteratorNext(&it, &tmp, true); Assert(tok == WJB_DONE); - return res; + return true; +} + +/* + * Emit correct, translatable cast error message + */ +static void +cannotCastJsonbValue(enum jbvType type, const char *sqltype) +{ + static const struct + { + enum jbvType type; + const char *msg; + } + messages[] = + { + { jbvNull, gettext_noop("cannot cast jsonb null to type %s") }, + { jbvString, gettext_noop("cannot cast jsonb string to type %s") }, + { jbvNumeric, gettext_noop("cannot cast jsonb numeric to type %s") }, + { jbvBool, gettext_noop("cannot cast jsonb boolean to type %s") }, + { jbvArray, gettext_noop("cannot cast jsonb array to type %s") }, + { jbvObject, gettext_noop("cannot cast jsonb object to type %s") }, + { jbvBinary, gettext_noop("cannot cast jsonb array or object to type %s") } + }; + int i; + + for(i=0; iroot, &v) || v.type != jbvBool) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("jsonb value must be boolean"))); + cannotCastJsonbValue(v.type, "boolean"); PG_FREE_IF_COPY(in, 0); @@ -1914,9 +1949,7 @@ jsonb_numeric(PG_FUNCTION_ARGS) Numeric retValue; if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("jsonb value must be numeric"))); + cannotCastJsonbValue(v.type, "numeric"); /* * v.val.numeric points into jsonb body, so we need to make a copy to @@ -1937,9 +1970,7 @@ jsonb_int2(PG_FUNCTION_ARGS) Datum retValue; if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("jsonb value must be numeric"))); + cannotCastJsonbValue(v.type, "smallint"); retValue = DirectFunctionCall1(numeric_int2, NumericGetDatum(v.val.numeric)); @@ -1957,9 +1988,7 @@ jsonb_int4(PG_FUNCTION_ARGS) Datum retValue; if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("jsonb value must be numeric"))); + cannotCastJsonbValue(v.type, "integer"); retValue = DirectFunctionCall1(numeric_int4, NumericGetDatum(v.val.numeric)); @@ -1977,9 +2006,7 @@ jsonb_int8(PG_FUNCTION_ARGS) Datum retValue; if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("jsonb value must be numeric"))); + cannotCastJsonbValue(v.type, "bigint"); retValue = DirectFunctionCall1(numeric_int8, NumericGetDatum(v.val.numeric)); @@ -1997,9 +2024,7 @@ jsonb_float4(PG_FUNCTION_ARGS) Datum retValue; if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("jsonb value must be numeric"))); + cannotCastJsonbValue(v.type, "real"); retValue = DirectFunctionCall1(numeric_float4, NumericGetDatum(v.val.numeric)); @@ -2017,9 +2042,7 @@ jsonb_float8(PG_FUNCTION_ARGS) Datum retValue; if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("jsonb value must be numeric"))); + cannotCastJsonbValue(v.type, "double precision"); retValue = DirectFunctionCall1(numeric_float8, NumericGetDatum(v.val.numeric)); diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 593abd74c3..3c37a8ed27 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -4321,7 +4321,7 @@ select 'true'::jsonb::bool; (1 row) select '[]'::jsonb::bool; -ERROR: jsonb value must be boolean +ERROR: cannot cast jsonb array to type boolean select '1.0'::jsonb::float; float8 -------- @@ -4329,7 +4329,7 @@ select '1.0'::jsonb::float; (1 row) select '[1.0]'::jsonb::float; -ERROR: jsonb value must be numeric +ERROR: cannot cast jsonb array to type double precision select '12345'::jsonb::int4; int4 ------- @@ -4337,7 +4337,7 @@ select '12345'::jsonb::int4; (1 row) select '"hello"'::jsonb::int4; -ERROR: jsonb value must be numeric +ERROR: cannot cast jsonb string to type integer select '12345'::jsonb::numeric; numeric --------- @@ -4345,7 +4345,7 @@ select '12345'::jsonb::numeric; (1 row) select '{}'::jsonb::numeric; -ERROR: jsonb value must be numeric +ERROR: cannot cast jsonb object to type numeric select '12345.05'::jsonb::numeric; numeric ---------- -- 2.40.0