pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Teodor Sigaev <teodor@sigaev.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Dagfinn Ilmari Mannsåker <ilmari@ilmari.org>
Cc: Aleksander Alekseev <a.alekseev@postgrespro.ru>
Cc: David Steele <david@pgmasters.net>
Cc: Nikita Glukhov <n.gluhov@postgrespro.ru>
Cc: Darafei Praliaskouski <me@komzpa.net>
Cc: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Cast jsonb to numeric, int, float, bool
Date: Tue, 8 May 2018 18:51:59 +0300
Message-ID: <dda6f9a9-4290-a85b-7d4d-e4b9968bac7e@sigaev.ru> (raw )
In-Reply-To: <2087.1525792633@sss.pgh.pa.us >
References: <151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org >
<76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru >
<d75ed4fe-574b-41c7-080f-ed662fa5d9ba@pgmasters.net >
<20180327145337.GA20311@e733.localdomain >
<ca0bdf99-98a5-3a44-3f49-13941e0634ca@sigaev.ru >
<912e1a07-90a9-ad97-bc70-d71ec0f6f073@sigaev.ru >
<be43822f-7fd2-c5c2-8435-6a896d1ed46c@sigaev.ru >
<CA+Tgmob08StTV9yu04D0idRFNMh+UoyKax5Otvrix7rEZC8rMw@mail.gmail.com >
<0250b45d-4a38-5453-cb5a-8fa9883da36b@sigaev.ru >
<20180330142119.GA1411@e733.localdomain >
<20180330142423.GB1411@e733.localdomain >
<d8j1sg1mvwp.fsf@dalvik.ping.uio.no >
<CA+TgmoaYgUkbrpzYgBPhH31+9BioCRs3RF2Bcz8E-+zttcCB4A@mail.gmail.com >
<bab83c35-2a25-927b-d28d-f836402c48ba@sigaev.ru >
<24822.1525789657@sss.pgh.pa.us >
<2087.1525792633@sss.pgh.pa.us >
>> 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/
Attachments:
jsonb_cast_err_msg-v2.patch (text/x-patch, 5.7K) download
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; i<lengthof(messages); i++)
+ if (messages[i].type == type)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(messages[i].msg, sqltype)));
+
+ /* should be unreachable */
+ elog(ERROR, "unknown jsonb type: %d", (int)type);
}
Datum
@@ -1897,9 +1934,7 @@ jsonb_bool(PG_FUNCTION_ARGS)
JsonbValue v;
if (!JsonbExtractScalar(&in->root, &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
----------
view thread (31+ messages) latest in thread
Message-ID: <dda6f9a9-4290-a85b-7d4d-e4b9968bac7e@sigaev.ru>
Permalink: ../dda6f9a9-4290-a85b-7d4d-e4b9968bac7e@sigaev.ru/
Also on: postgresql.org/message-id/dda6f9a9-4290-a85b-7d4d-e4b9968bac7e@sigaev.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: teodor@sigaev.ru, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, ilmari@ilmari.org, a.alekseev@postgrespro.ru, david@pgmasters.net, n.gluhov@postgrespro.ru, me@komzpa.net, a.lubennikova@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Cast jsonb to numeric, int, float, bool
In-Reply-To: <dda6f9a9-4290-a85b-7d4d-e4b9968bac7e@sigaev.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