Add casts from jsonb

Add explicit cast from scalar jsonb to all numeric and bool types. It would be
better to have cast from scalar jsonb to text too but there is already a cast
from jsonb to text as just text representation of json. There is no way to have
two different casts for the same type's pair.

Bump catalog version

Author: Anastasia Lubennikova with editorization by Nikita Glukhov and me
Review by: Aleksander Alekseev, Nikita Glukhov, Darafei Praliaskouski
Discussion: https://www.postgresql.org/message-id/flat/0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru

1 files changed, 175 insertions, 0 deletions

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 0f701801641..80d23cc0524 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1845,3 +1845,178 @@ jsonb_object_agg_finalfn(PG_FUNCTION_ARGS)
 
 	PG_RETURN_POINTER(out);
 }
+
+
+/*
+ * Extract scalar value from raw-scalar pseudo-array jsonb.
+ */
+static JsonbValue *
+JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res)
+{
+	JsonbIterator *it;
+	JsonbIteratorToken tok PG_USED_FOR_ASSERTS_ONLY;
+	JsonbValue	tmp;
+
+	if (!JsonContainerIsArray(jbc) || !JsonContainerIsScalar(jbc))
+		return NULL;
+
+	/*
+	 * A root scalar is stored as an array of one element, so we get the
+	 * array and then its first (and only) member.
+	 */
+	it = JsonbIteratorInit(jbc);
+
+	tok = JsonbIteratorNext(&it, &tmp, true);
+	Assert(tok == WJB_BEGIN_ARRAY);
+	Assert(tmp.val.array.nElems == 1 && tmp.val.array.rawScalar);
+
+	tok = JsonbIteratorNext(&it, res, true);
+	Assert (tok == WJB_ELEM);
+	Assert(IsAJsonbScalar(res));
+
+	tok = JsonbIteratorNext(&it, &tmp, true);
+	Assert (tok == WJB_END_ARRAY);
+
+	tok = JsonbIteratorNext(&it, &tmp, true);
+	Assert(tok == WJB_DONE);
+
+	return res;
+}
+
+Datum
+jsonb_bool(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *in = PG_GETARG_JSONB_P(0);
+	JsonbValue	v;
+
+	if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvBool)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("jsonb value must be boolean")));
+
+	PG_FREE_IF_COPY(in, 0);
+
+	PG_RETURN_BOOL(v.val.boolean);
+}
+
+Datum
+jsonb_numeric(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *in = PG_GETARG_JSONB_P(0);
+	JsonbValue	v;
+	Numeric		retValue;
+
+	if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("jsonb value must be numeric")));
+
+	/*
+	 * v.val.numeric points into jsonb body, so we need to make a copy to return
+	 */
+	retValue = DatumGetNumericCopy(NumericGetDatum(v.val.numeric));
+
+	PG_FREE_IF_COPY(in, 0);
+
+	PG_RETURN_NUMERIC(retValue);
+}
+
+Datum
+jsonb_int2(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *in = PG_GETARG_JSONB_P(0);
+	JsonbValue  v;
+	Datum		retValue;
+
+	if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("jsonb value must be numeric")));
+
+	retValue = DirectFunctionCall1(numeric_int2,
+								   NumericGetDatum(v.val.numeric));
+
+	PG_FREE_IF_COPY(in, 0);
+
+	PG_RETURN_DATUM(retValue);
+}
+
+Datum
+jsonb_int4(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *in = PG_GETARG_JSONB_P(0);
+	JsonbValue  v;
+	Datum		retValue;
+
+	if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("jsonb value must be numeric")));
+
+	retValue = DirectFunctionCall1(numeric_int4,
+								   NumericGetDatum(v.val.numeric));
+
+	PG_FREE_IF_COPY(in, 0);
+
+	PG_RETURN_DATUM(retValue);
+}
+
+Datum
+jsonb_int8(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *in = PG_GETARG_JSONB_P(0);
+	JsonbValue  v;
+	Datum		retValue;
+
+	if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("jsonb value must be numeric")));
+
+	retValue = DirectFunctionCall1(numeric_int8,
+								   NumericGetDatum(v.val.numeric));
+
+	PG_FREE_IF_COPY(in, 0);
+
+	PG_RETURN_DATUM(retValue);
+}
+
+Datum
+jsonb_float4(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *in = PG_GETARG_JSONB_P(0);
+	JsonbValue	v;
+	Datum		retValue;
+
+	if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("jsonb value must be numeric")));
+
+	retValue = DirectFunctionCall1(numeric_float4,
+								   NumericGetDatum(v.val.numeric));
+
+	PG_FREE_IF_COPY(in, 0);
+
+	PG_RETURN_DATUM(retValue);
+}
+
+Datum
+jsonb_float8(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *in = PG_GETARG_JSONB_P(0);
+	JsonbValue	v;
+	Datum		retValue;
+
+	if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("jsonb value must be numeric")));
+
+	retValue = DirectFunctionCall1(numeric_float8,
+								   NumericGetDatum(v.val.numeric));
+
+	PG_FREE_IF_COPY(in, 0);
+
+	PG_RETURN_DATUM(retValue);
+}