From fd71e22f087af2cd14c4a9101c96efb56888ab72 Mon Sep 17 00:00:00 2001 From: Anastasia Date: Thu, 1 Mar 2018 01:55:31 +0300 Subject: [PATCH] Cast jsonbNumeric to numeric, int4 and float8. Cast jsonbBool to bool. --- src/backend/utils/adt/jsonb.c | 95 +++++++++++++++++++++++++++++++++++++++++++ src/include/catalog/pg_cast.h | 5 +++ src/include/catalog/pg_proc.h | 9 ++++ 3 files changed, 109 insertions(+) diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index 0f70180..fb907f2 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 b447818..ea765df 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 c00d055..2f84978 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2477,6 +2477,15 @@ DESCR("convert int2 to numeric"); DATA(insert OID = 1783 ( int2 PGNSP PGUID 12 1 0 0 0 f 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 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 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 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 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 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"); -- 2.7.4