Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fG4uO-0005oN-Tz for pgsql-hackers@arkaria.postgresql.org; Tue, 08 May 2018 15:52:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fG4uN-00031v-BY for pgsql-hackers@arkaria.postgresql.org; Tue, 08 May 2018 15:52:07 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fG4uN-00031m-0u for pgsql-hackers@lists.postgresql.org; Tue, 08 May 2018 15:52:07 +0000 Received: from hook.sigaev.ru ([93.180.27.50]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fG4uJ-0007ob-19 for pgsql-hackers@lists.postgresql.org; Tue, 08 May 2018 15:52:05 +0000 Received: from xor.sai.msu.ru (unknown [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: teodor) by hook.sigaev.ru (Postfix) with ESMTPSA id A953BB19192; Tue, 8 May 2018 18:51:59 +0300 (MSK) Subject: Re: Cast jsonb to numeric, int, float, bool To: Tom Lane Cc: Robert Haas , =?UTF-8?Q?Dagfinn_Ilmari_Manns=c3=a5ker?= , Aleksander Alekseev , David Steele , Nikita Glukhov , Darafei Praliaskouski , Anastasia Lubennikova , PostgreSQL Hackers References: <151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org> <76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru> <20180327145337.GA20311@e733.localdomain> <912e1a07-90a9-ad97-bc70-d71ec0f6f073@sigaev.ru> <0250b45d-4a38-5453-cb5a-8fa9883da36b@sigaev.ru> <20180330142119.GA1411@e733.localdomain> <20180330142423.GB1411@e733.localdomain> <24822.1525789657@sss.pgh.pa.us> <2087.1525792633@sss.pgh.pa.us> From: Teodor Sigaev Message-ID: Date: Tue, 8 May 2018 18:51:59 +0300 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101 Firefox/52.0 SeaMonkey/2.49.3 MIME-Version: 1.0 In-Reply-To: <2087.1525792633@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------447E1B053DF05EFD1D2A9DEE" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------447E1B053DF05EFD1D2A9DEE Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: 7bit >> 1) Does this really pass muster from the translatability standpoint? >> I doubt it. Huh, I missed that. > I think you want the callers to look like > > if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) > cannotCastJsonbValue(v.type, "double precision"); > > where the subroutine contains the whole ereport() call, and its lookup > table entries are e.g. > > gettext_noop("cannot cast jsonb string to type %s") Thanks for your idea, patch is attached -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/ --------------447E1B053DF05EFD1D2A9DEE Content-Type: text/x-patch; name="jsonb_cast_err_msg-v2.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jsonb_cast_err_msg-v2.patch" diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index 9d2b89f90c..3ef71bbade 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, translable cast error message + */ +static void +cannotCastJsonbValue(enum jbvType type, const char *sqltype) +{ + static struct + { + enum jbvType type; + 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 f705417b09..23fd77a8d0 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 ---------- --------------447E1B053DF05EFD1D2A9DEE--