Hello everyone, > Since this patch was updated after being set a Ready for Committer and > there appear to be some open questions, I have set it to Needs Review.
I decided to take a look. The patch didn't apply after changes made in fd1a421f, but I fixed that. Also there were no tests. I fixed that too. I believe it's "Ready for Committer". -- Best regards, Aleksander Alekseev
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 0f70180164..fb907f2a73 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1845,3 +1845,98 @@ 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_numeric(PG_FUNCTION_ARGS)
+{
+ Jsonb *in = PG_GETARG_JSONB_P(0);
+ JsonbValue v;
+
+ if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("jsonb value must be numeric")));
+
+ PG_RETURN_NUMERIC(v.val.numeric);
+}
+
+Datum
+jsonb_int4(PG_FUNCTION_ARGS)
+{
+ Jsonb *in = PG_GETARG_JSONB_P(0);
+ JsonbValue v;
+
+ if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("jsonb value must be numeric")));
+
+ PG_RETURN_INT32(DatumGetInt32(DirectFunctionCall1(numeric_int4,
+
NumericGetDatum(v.val.numeric))));
+}
+
+Datum
+jsonb_float8(PG_FUNCTION_ARGS)
+{
+ Jsonb *in = PG_GETARG_JSONB_P(0);
+ JsonbValue v;
+
+ if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("jsonb value must be numeric")));
+
+ PG_RETURN_FLOAT8(DatumGetFloat8(DirectFunctionCall1(numeric_float8,
+
NumericGetDatum(v.val.numeric))));
+}
+
+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_RETURN_BOOL(v.val.boolean);
+}
diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h
index b4478188ca..ea765df80e 100644
--- a/src/include/catalog/pg_cast.h
+++ b/src/include/catalog/pg_cast.h
@@ -391,5 +391,10 @@ DATA(insert ( 1700 1700 1703 i f ));
/* json to/from jsonb */
DATA(insert ( 114 3802 0 a i ));
DATA(insert ( 3802 114 0 a i ));
+/* jsonb to numeric types */
+DATA(insert ( 3802 1700 4001 e f ));
+DATA(insert ( 3802 23 4002 e f ));
+DATA(insert ( 3802 701 4003 e f ));
+DATA(insert ( 3802 16 4004 e f ));
#endif /* PG_CAST_H */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index bfc90098f8..0d5d33985d 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2475,6 +2475,16 @@ DESCR("convert int2 to numeric");
DATA(insert OID = 1783 ( int2 PGNSP PGUID 12
1 0 0 0 f f f t f i s 1 0 21 "1700" _null_ _null_ _null_ _null_ _null_
numeric_int2 _null_ _null_ _null_ ));
DESCR("convert numeric to int2");
+
+DATA(insert OID = 4001 ( numeric PGNSP PGUID 12 1 0 0 0
f f f t f i s 1 0 1700 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_numeric
_null_ _null_ _null_ ));
+DESCR("convert jsonb to numeric");
+DATA(insert OID = 4002 ( int4 PGNSP PGUID 12 1 0 0 0
f f f t f i s 1 0 23 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_int4
_null_ _null_ _null_ ));
+DESCR("convert jsonb to int4");
+DATA(insert OID = 4003 ( float8 PGNSP PGUID 12 1 0 0 0
f f f t f i s 1 0 701 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_float8
_null_ _null_ _null_ ));
+DESCR("convert jsonb to float8");
+DATA(insert OID = 4004 ( bool PGNSP PGUID 12 1 0 0 0 f f f t
f i s 1 0 16 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_bool _null_
_null_ _null_ ));
+DESCR("convert jsonb to boolean");
+
/* formatting */
DATA(insert OID = 1770 ( to_char PGNSP PGUID 12 1 0 0 0
f f f t f s s 2 0 25 "1184 25" _null_ _null_ _null_ _null_ _null_
timestamptz_to_char _null_ _null_ _null_ ));
DESCR("format timestamp with time zone to text");
diff --git a/src/test/regress/expected/jsonb.out
b/src/test/regress/expected/jsonb.out
index 465195a317..f577ad6aa5 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4191,3 +4191,36 @@ select ts_headline('[]'::jsonb, tsquery('aaa & bbb'));
[]
(1 row)
+-- casts
+select 'true'::jsonb::bool;
+ bool
+------
+ t
+(1 row)
+
+select '[]'::jsonb::bool;
+ERROR: jsonb value must be boolean
+select '1.0'::jsonb::float;
+ float8
+--------
+ 1
+(1 row)
+
+select '[1.0]'::jsonb::float;
+ERROR: jsonb value must be numeric
+select '12345'::jsonb::int4;
+ int4
+-------
+ 12345
+(1 row)
+
+select '"hello"'::jsonb::int4;
+ERROR: jsonb value must be numeric
+select '12345'::jsonb::numeric;
+ numeric
+---------
+ 12345
+(1 row)
+
+select '{}'::jsonb::numeric;
+ERROR: jsonb value must be numeric
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 903e5ef67d..9c2158f8c6 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1105,3 +1105,13 @@ select ts_headline('english', '{"a": "aaa bbb", "b":
{"c": "ccc ddd fff", "c1":
select ts_headline('null'::jsonb, tsquery('aaa & bbb'));
select ts_headline('{}'::jsonb, tsquery('aaa & bbb'));
select ts_headline('[]'::jsonb, tsquery('aaa & bbb'));
+
+-- casts
+select 'true'::jsonb::bool;
+select '[]'::jsonb::bool;
+select '1.0'::jsonb::float;
+select '[1.0]'::jsonb::float;
+select '12345'::jsonb::int4;
+select '"hello"'::jsonb::int4;
+select '12345'::jsonb::numeric;
+select '{}'::jsonb::numeric;
signature.asc
Description: PGP signature
