diff options
| -rw-r--r-- | src/backend/utils/adt/jsonb.c | 175 | ||||
| -rw-r--r-- | src/include/catalog/catversion.h | 2 | ||||
| -rw-r--r-- | src/include/catalog/pg_cast.h | 9 | ||||
| -rw-r--r-- | src/include/catalog/pg_proc.h | 16 | ||||
| -rw-r--r-- | src/test/regress/expected/jsonb.out | 105 | ||||
| -rw-r--r-- | src/test/regress/sql/jsonb.sql | 22 |
6 files changed, 328 insertions, 1 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); +} diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index bfc2bd125d7..d4a7b23f801 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201803271 +#define CATALOG_VERSION_NO 201803291 #endif diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h index b4478188cab..c47fb5bd0dd 100644 --- a/src/include/catalog/pg_cast.h +++ b/src/include/catalog/pg_cast.h @@ -392,4 +392,13 @@ DATA(insert ( 1700 1700 1703 i f )); DATA(insert ( 114 3802 0 a i )); DATA(insert ( 3802 114 0 a i )); +/* jsonb to numeric and bool types */ +DATA(insert ( 3802 16 3556 e f )); +DATA(insert ( 3802 1700 3449 e f )); +DATA(insert ( 3802 21 3450 e f )); +DATA(insert ( 3802 23 3451 e f )); +DATA(insert ( 3802 20 3452 e f )); +DATA(insert ( 3802 700 3453 e f )); +DATA(insert ( 3802 701 2580 e f )); + #endif /* PG_CAST_H */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index bfc90098f86..ec50afcdf00 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2475,6 +2475,22 @@ 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 = 3556 ( 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"); +DATA(insert OID = 3449 ( 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 = 3450 ( int2 PGNSP PGUID 12 1 0 0 0 f f f t f i s 1 0 21 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_int2 _null_ _null_ _null_ )); +DESCR("convert jsonb to int2"); +DATA(insert OID = 3451 ( 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 = 3452 ( int8 PGNSP PGUID 12 1 0 0 0 f f f t f i s 1 0 20 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_int8 _null_ _null_ _null_ )); +DESCR("convert jsonb to int8"); +DATA(insert OID = 3453 ( float4 PGNSP PGUID 12 1 0 0 0 f f f t f i s 1 0 700 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_float4 _null_ _null_ _null_ )); +DESCR("convert jsonb to float4"); +DATA(insert OID = 2580 ( 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"); + /* 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 465195a3172..f8d6e6f7ccd 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -4191,3 +4191,108 @@ 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 +select '12345.05'::jsonb::numeric; + numeric +---------- + 12345.05 +(1 row) + +select '12345.05'::jsonb::float4; + float4 +-------- + 12345 +(1 row) + +select '12345.05'::jsonb::float8; + float8 +---------- + 12345.05 +(1 row) + +select '12345.05'::jsonb::int2; + int2 +------- + 12345 +(1 row) + +select '12345.05'::jsonb::int4; + int4 +------- + 12345 +(1 row) + +select '12345.05'::jsonb::int8; + int8 +------- + 12345 +(1 row) + +select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric; + numeric +------------------------------------------------------ + 12345.0000000000000000000000000000000000000000000005 +(1 row) + +select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4; + float4 +-------- + 12345 +(1 row) + +select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8; + float8 +-------- + 12345 +(1 row) + +select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2; + int2 +------- + 12345 +(1 row) + +select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4; + int4 +------- + 12345 +(1 row) + +select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; + int8 +------- + 12345 +(1 row) + diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 903e5ef67d8..2439f949bd5 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1105,3 +1105,25 @@ 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; +select '12345.05'::jsonb::numeric; +select '12345.05'::jsonb::float4; +select '12345.05'::jsonb::float8; +select '12345.05'::jsonb::int2; +select '12345.05'::jsonb::int4; +select '12345.05'::jsonb::int8; +select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric; +select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4; +select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8; +select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2; +select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4; +select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8; |