Date: Tue, 27 Mar 2018 17:53:37 +0300
From: Aleksander Alekseev <a.alekseev@postgrespro.ru>
To: David Steele <david@pgmasters.net>
Cc: Nikita Glukhov <n.gluhov@postgrespro.ru>,
	Darafei Praliaskouski <me@komzpa.net>,
	Anastasia Lubennikova <a.lubennikova@postgrespro.ru>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: Re: Cast jsonb to numeric, int, float, bool
Message-ID: <20180327145337.GA20311@e733.localdomain>
References: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
 <9c013081-5dc0-1b87-a559-a0c2d405727a@postgrespro.ru>
 <151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org>
 <76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru>
 <d75ed4fe-574b-41c7-080f-ed662fa5d9ba@pgmasters.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha256;
	protocol="application/pgp-signature"; boundary="yNb1oOkm5a9FJOVX"
Content-Disposition: inline
In-Reply-To: <d75ed4fe-574b-41c7-080f-ed662fa5d9ba@pgmasters.net>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk


--yNb1oOkm5a9FJOVX
Content-Type: multipart/mixed; boundary="8t9RHnE3ZwKMSgU+"
Content-Disposition: inline


--8t9RHnE3ZwKMSgU+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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".

--=20
Best regards,
Aleksander Alekseev

--8t9RHnE3ZwKMSgU+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="jsonb-casts-v4.patch"
Content-Transfer-Encoding: quoted-printable

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)
=20
 	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 =3D JsonbIteratorInit(jbc);
+
+	tok =3D JsonbIteratorNext(&it, &tmp, true);
+	Assert(tok =3D=3D WJB_BEGIN_ARRAY);
+	Assert(tmp.val.array.nElems =3D=3D 1 && tmp.val.array.rawScalar);
+
+	tok =3D JsonbIteratorNext(&it, res, true);
+	Assert (tok =3D=3D WJB_ELEM);
+	Assert(IsAJsonbScalar(res));
+
+	tok =3D JsonbIteratorNext(&it, &tmp, true);
+	Assert (tok =3D=3D WJB_END_ARRAY);
+
+	tok =3D JsonbIteratorNext(&it, &tmp, true);
+	Assert(tok =3D=3D WJB_DONE);
+
+	return res;
+}
+
+Datum
+jsonb_numeric(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *in =3D PG_GETARG_JSONB_P(0);
+	JsonbValue	v;
+
+	if (!JsonbExtractScalar(&in->root, &v) || v.type !=3D 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 =3D PG_GETARG_JSONB_P(0);
+	JsonbValue  v;
+
+	if (!JsonbExtractScalar(&in->root, &v) || v.type !=3D 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 =3D PG_GETARG_JSONB_P(0);
+	JsonbValue	v;
+
+	if (!JsonbExtractScalar(&in->root, &v) || v.type !=3D 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 =3D PG_GETARG_JSONB_P(0);
+	JsonbValue	v;
+
+	if (!JsonbExtractScalar(&in->root, &v) || v.type !=3D 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 ));
=20
 #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 =3D 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");
=20
+
+DATA(insert OID =3D 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_ _n=
ull_ _null_ ));
+DESCR("convert jsonb to numeric");
+DATA(insert OID =3D 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_ _n=
ull_ ));
+DESCR("convert jsonb to int4");
+DATA(insert OID =3D 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 =3D 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_ _nu=
ll_ ));
+DESCR("convert jsonb to boolean");
+
 /* formatting */
 DATA(insert OID =3D 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 _n=
ull_ _null_ _null_ ));
 DESCR("format timestamp with time zone to text");
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expecte=
d/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)
=20
+-- casts
+select 'true'::jsonb::bool;
+ bool=20
+------
+ t
+(1 row)
+
+select '[]'::jsonb::bool;
+ERROR:  jsonb value must be boolean
+select '1.0'::jsonb::float;
+ float8=20
+--------
+      1
+(1 row)
+
+select '[1.0]'::jsonb::float;
+ERROR:  jsonb value must be numeric
+select '12345'::jsonb::int4;
+ int4 =20
+-------
+ 12345
+(1 row)
+
+select '"hello"'::jsonb::int4;
+ERROR:  jsonb value must be numeric
+select '12345'::jsonb::numeric;
+ numeric=20
+---------
+   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;

--8t9RHnE3ZwKMSgU+--

--yNb1oOkm5a9FJOVX
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCAAdFiEECAokn0QZCoIHW4GpEEnCBLXCDtYFAlq6Wu4ACgkQEEnCBLXC
DtaG3A/6A0vy/qMFmQCcKC+7AkN1691Nic3nxbPlNdyoEfkju2o+o5c6WH6HkIoD
0060WBGnEr+RRze8VX2ydxlxuDD4GioGUqGgWcQKp1samRpGeNa+q1sCctTsWBHX
v6Ht/MzO8vekAiY2BIq9zem/HrSkf1k30JNN2SQrCIiLwf5sZ2drYm4+uHMSTGRM
eVO318+YHk+0wyW2hhQyXKY1EtCg2OXEppiEfUoZtq/OkQIQ5QgOy6Ev+nHKDT/y
PP+chhKCj74VPzXLCbRd95ri9h+QMieQsB/9LGjIcKCi8XrnWhhVA86OeiDUjO7k
UtjkyIb++KgYFB4nGZa+baRB43vZeeIUMzSHBFG6V2uwjALGY6WWwFtiME1NNioz
HuiKBCLOGnhHs5ADj6a4gGJLBKh3sxL0XWXHEPzflrhtFvgaL7146LSEHcugkOig
Vc3Z0UrpbvAcaBBmdPCFAbQoY9x/wNb563p7TRjqvfg1U5Nuiz2Tat7zMKMNSZnc
e6Pq0sQweRalC8IvKnKFaYuCzbSk5uMhJ6H7j1icWUu0aONdUAKv1yj7sj6LB7Wa
TfM77z+9d60yCDHjHlrYsj+zyzPSAjCIa2EQYVsAkac8m1GKww8TnWofHZmm0Qm8
GLF2ggJwRJEPxGaq15NVawoPrwTd4yWszJktSy41RngkhSQgNAs=
=/1bZ
-----END PGP SIGNATURE-----

--yNb1oOkm5a9FJOVX--