Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc0cbe00

Browse files
committed
Add casts from jsonb
Add explicit cast from scalar jsonb to all numeric and bool types. It would bebetter to have cast from scalar jsonb to text too but there is already a castfrom jsonb to text as just text representation of json. There is no way to havetwo different casts for the same type's pair.Bump catalog versionAuthor: Anastasia Lubennikova with editorization by Nikita Glukhov and meReview by: Aleksander Alekseev, Nikita Glukhov, Darafei PraliaskouskiDiscussion:https://www.postgresql.org/message-id/flat/0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru
1 parent7fe04ce commitc0cbe00

File tree

6 files changed

+328
-1
lines changed

6 files changed

+328
-1
lines changed

‎src/backend/utils/adt/jsonb.c

Lines changed: 175 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1845,3 +1845,178 @@ jsonb_object_agg_finalfn(PG_FUNCTION_ARGS)
18451845

18461846
PG_RETURN_POINTER(out);
18471847
}
1848+
1849+
1850+
/*
1851+
* Extract scalar value from raw-scalar pseudo-array jsonb.
1852+
*/
1853+
staticJsonbValue*
1854+
JsonbExtractScalar(JsonbContainer*jbc,JsonbValue*res)
1855+
{
1856+
JsonbIterator*it;
1857+
JsonbIteratorTokentokPG_USED_FOR_ASSERTS_ONLY;
1858+
JsonbValuetmp;
1859+
1860+
if (!JsonContainerIsArray(jbc)|| !JsonContainerIsScalar(jbc))
1861+
returnNULL;
1862+
1863+
/*
1864+
* A root scalar is stored as an array of one element, so we get the
1865+
* array and then its first (and only) member.
1866+
*/
1867+
it=JsonbIteratorInit(jbc);
1868+
1869+
tok=JsonbIteratorNext(&it,&tmp, true);
1870+
Assert(tok==WJB_BEGIN_ARRAY);
1871+
Assert(tmp.val.array.nElems==1&&tmp.val.array.rawScalar);
1872+
1873+
tok=JsonbIteratorNext(&it,res, true);
1874+
Assert (tok==WJB_ELEM);
1875+
Assert(IsAJsonbScalar(res));
1876+
1877+
tok=JsonbIteratorNext(&it,&tmp, true);
1878+
Assert (tok==WJB_END_ARRAY);
1879+
1880+
tok=JsonbIteratorNext(&it,&tmp, true);
1881+
Assert(tok==WJB_DONE);
1882+
1883+
returnres;
1884+
}
1885+
1886+
Datum
1887+
jsonb_bool(PG_FUNCTION_ARGS)
1888+
{
1889+
Jsonb*in=PG_GETARG_JSONB_P(0);
1890+
JsonbValuev;
1891+
1892+
if (!JsonbExtractScalar(&in->root,&v)||v.type!=jbvBool)
1893+
ereport(ERROR,
1894+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1895+
errmsg("jsonb value must be boolean")));
1896+
1897+
PG_FREE_IF_COPY(in,0);
1898+
1899+
PG_RETURN_BOOL(v.val.boolean);
1900+
}
1901+
1902+
Datum
1903+
jsonb_numeric(PG_FUNCTION_ARGS)
1904+
{
1905+
Jsonb*in=PG_GETARG_JSONB_P(0);
1906+
JsonbValuev;
1907+
NumericretValue;
1908+
1909+
if (!JsonbExtractScalar(&in->root,&v)||v.type!=jbvNumeric)
1910+
ereport(ERROR,
1911+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1912+
errmsg("jsonb value must be numeric")));
1913+
1914+
/*
1915+
* v.val.numeric points into jsonb body, so we need to make a copy to return
1916+
*/
1917+
retValue=DatumGetNumericCopy(NumericGetDatum(v.val.numeric));
1918+
1919+
PG_FREE_IF_COPY(in,0);
1920+
1921+
PG_RETURN_NUMERIC(retValue);
1922+
}
1923+
1924+
Datum
1925+
jsonb_int2(PG_FUNCTION_ARGS)
1926+
{
1927+
Jsonb*in=PG_GETARG_JSONB_P(0);
1928+
JsonbValuev;
1929+
DatumretValue;
1930+
1931+
if (!JsonbExtractScalar(&in->root,&v)||v.type!=jbvNumeric)
1932+
ereport(ERROR,
1933+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1934+
errmsg("jsonb value must be numeric")));
1935+
1936+
retValue=DirectFunctionCall1(numeric_int2,
1937+
NumericGetDatum(v.val.numeric));
1938+
1939+
PG_FREE_IF_COPY(in,0);
1940+
1941+
PG_RETURN_DATUM(retValue);
1942+
}
1943+
1944+
Datum
1945+
jsonb_int4(PG_FUNCTION_ARGS)
1946+
{
1947+
Jsonb*in=PG_GETARG_JSONB_P(0);
1948+
JsonbValuev;
1949+
DatumretValue;
1950+
1951+
if (!JsonbExtractScalar(&in->root,&v)||v.type!=jbvNumeric)
1952+
ereport(ERROR,
1953+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1954+
errmsg("jsonb value must be numeric")));
1955+
1956+
retValue=DirectFunctionCall1(numeric_int4,
1957+
NumericGetDatum(v.val.numeric));
1958+
1959+
PG_FREE_IF_COPY(in,0);
1960+
1961+
PG_RETURN_DATUM(retValue);
1962+
}
1963+
1964+
Datum
1965+
jsonb_int8(PG_FUNCTION_ARGS)
1966+
{
1967+
Jsonb*in=PG_GETARG_JSONB_P(0);
1968+
JsonbValuev;
1969+
DatumretValue;
1970+
1971+
if (!JsonbExtractScalar(&in->root,&v)||v.type!=jbvNumeric)
1972+
ereport(ERROR,
1973+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1974+
errmsg("jsonb value must be numeric")));
1975+
1976+
retValue=DirectFunctionCall1(numeric_int8,
1977+
NumericGetDatum(v.val.numeric));
1978+
1979+
PG_FREE_IF_COPY(in,0);
1980+
1981+
PG_RETURN_DATUM(retValue);
1982+
}
1983+
1984+
Datum
1985+
jsonb_float4(PG_FUNCTION_ARGS)
1986+
{
1987+
Jsonb*in=PG_GETARG_JSONB_P(0);
1988+
JsonbValuev;
1989+
DatumretValue;
1990+
1991+
if (!JsonbExtractScalar(&in->root,&v)||v.type!=jbvNumeric)
1992+
ereport(ERROR,
1993+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1994+
errmsg("jsonb value must be numeric")));
1995+
1996+
retValue=DirectFunctionCall1(numeric_float4,
1997+
NumericGetDatum(v.val.numeric));
1998+
1999+
PG_FREE_IF_COPY(in,0);
2000+
2001+
PG_RETURN_DATUM(retValue);
2002+
}
2003+
2004+
Datum
2005+
jsonb_float8(PG_FUNCTION_ARGS)
2006+
{
2007+
Jsonb*in=PG_GETARG_JSONB_P(0);
2008+
JsonbValuev;
2009+
DatumretValue;
2010+
2011+
if (!JsonbExtractScalar(&in->root,&v)||v.type!=jbvNumeric)
2012+
ereport(ERROR,
2013+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
2014+
errmsg("jsonb value must be numeric")));
2015+
2016+
retValue=DirectFunctionCall1(numeric_float8,
2017+
NumericGetDatum(v.val.numeric));
2018+
2019+
PG_FREE_IF_COPY(in,0);
2020+
2021+
PG_RETURN_DATUM(retValue);
2022+
}

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201803271
56+
#defineCATALOG_VERSION_NO201803291
5757

5858
#endif

‎src/include/catalog/pg_cast.h

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -392,4 +392,13 @@ DATA(insert ( 1700 1700 1703 i f ));
392392
DATA(insert (11438020ai ));
393393
DATA(insert (38021140ai ));
394394

395+
/* jsonb to numeric and bool types */
396+
DATA(insert (3802163556ef ));
397+
DATA(insert (380217003449ef ));
398+
DATA(insert (3802213450ef ));
399+
DATA(insert (3802233451ef ));
400+
DATA(insert (3802203452ef ));
401+
DATA(insert (38027003453ef ));
402+
DATA(insert (38027012580ef ));
403+
395404
#endif/* PG_CAST_H */

‎src/include/catalog/pg_proc.h

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2475,6 +2475,22 @@ DESCR("convert int2 to numeric");
24752475
DATA(insert OID = 1783 ( int2PGNSP 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_ ));
24762476
DESCR("convert numeric to int2");
24772477

2478+
2479+
DATA(insert OID = 3556 ( boolPGNSP 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_ ));
2480+
DESCR("convert jsonb to boolean");
2481+
DATA(insert OID = 3449 ( numericPGNSP 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_ ));
2482+
DESCR("convert jsonb to numeric");
2483+
DATA(insert OID = 3450 ( int2PGNSP 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_ ));
2484+
DESCR("convert jsonb to int2");
2485+
DATA(insert OID = 3451 ( int4PGNSP 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_ ));
2486+
DESCR("convert jsonb to int4");
2487+
DATA(insert OID = 3452 ( int8PGNSP 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_ ));
2488+
DESCR("convert jsonb to int8");
2489+
DATA(insert OID = 3453 ( float4PGNSP 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_ ));
2490+
DESCR("convert jsonb to float4");
2491+
DATA(insert OID = 2580 ( float8PGNSP 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_ ));
2492+
DESCR("convert jsonb to float8");
2493+
24782494
/* formatting */
24792495
DATA(insert OID = 1770 ( to_charPGNSP 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_ ));
24802496
DESCR("format timestamp with time zone to text");

‎src/test/regress/expected/jsonb.out

Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4191,3 +4191,108 @@ select ts_headline('[]'::jsonb, tsquery('aaa & bbb'));
41914191
[]
41924192
(1 row)
41934193

4194+
-- casts
4195+
select 'true'::jsonb::bool;
4196+
bool
4197+
------
4198+
t
4199+
(1 row)
4200+
4201+
select '[]'::jsonb::bool;
4202+
ERROR: jsonb value must be boolean
4203+
select '1.0'::jsonb::float;
4204+
float8
4205+
--------
4206+
1
4207+
(1 row)
4208+
4209+
select '[1.0]'::jsonb::float;
4210+
ERROR: jsonb value must be numeric
4211+
select '12345'::jsonb::int4;
4212+
int4
4213+
-------
4214+
12345
4215+
(1 row)
4216+
4217+
select '"hello"'::jsonb::int4;
4218+
ERROR: jsonb value must be numeric
4219+
select '12345'::jsonb::numeric;
4220+
numeric
4221+
---------
4222+
12345
4223+
(1 row)
4224+
4225+
select '{}'::jsonb::numeric;
4226+
ERROR: jsonb value must be numeric
4227+
select '12345.05'::jsonb::numeric;
4228+
numeric
4229+
----------
4230+
12345.05
4231+
(1 row)
4232+
4233+
select '12345.05'::jsonb::float4;
4234+
float4
4235+
--------
4236+
12345
4237+
(1 row)
4238+
4239+
select '12345.05'::jsonb::float8;
4240+
float8
4241+
----------
4242+
12345.05
4243+
(1 row)
4244+
4245+
select '12345.05'::jsonb::int2;
4246+
int2
4247+
-------
4248+
12345
4249+
(1 row)
4250+
4251+
select '12345.05'::jsonb::int4;
4252+
int4
4253+
-------
4254+
12345
4255+
(1 row)
4256+
4257+
select '12345.05'::jsonb::int8;
4258+
int8
4259+
-------
4260+
12345
4261+
(1 row)
4262+
4263+
select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric;
4264+
numeric
4265+
------------------------------------------------------
4266+
12345.0000000000000000000000000000000000000000000005
4267+
(1 row)
4268+
4269+
select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4;
4270+
float4
4271+
--------
4272+
12345
4273+
(1 row)
4274+
4275+
select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
4276+
float8
4277+
--------
4278+
12345
4279+
(1 row)
4280+
4281+
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
4282+
int2
4283+
-------
4284+
12345
4285+
(1 row)
4286+
4287+
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
4288+
int4
4289+
-------
4290+
12345
4291+
(1 row)
4292+
4293+
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
4294+
int8
4295+
-------
4296+
12345
4297+
(1 row)
4298+

‎src/test/regress/sql/jsonb.sql

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1105,3 +1105,25 @@ select ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1":
11051105
select ts_headline('null'::jsonb, tsquery('aaa & bbb'));
11061106
select ts_headline('{}'::jsonb, tsquery('aaa & bbb'));
11071107
select ts_headline('[]'::jsonb, tsquery('aaa & bbb'));
1108+
1109+
-- casts
1110+
select'true'::jsonb::bool;
1111+
select'[]'::jsonb::bool;
1112+
select'1.0'::jsonb::float;
1113+
select'[1.0]'::jsonb::float;
1114+
select'12345'::jsonb::int4;
1115+
select'"hello"'::jsonb::int4;
1116+
select'12345'::jsonb::numeric;
1117+
select'{}'::jsonb::numeric;
1118+
select'12345.05'::jsonb::numeric;
1119+
select'12345.05'::jsonb::float4;
1120+
select'12345.05'::jsonb::float8;
1121+
select'12345.05'::jsonb::int2;
1122+
select'12345.05'::jsonb::int4;
1123+
select'12345.05'::jsonb::int8;
1124+
select'12345.0000000000000000000000000000000000000000000005'::jsonb::numeric;
1125+
select'12345.0000000000000000000000000000000000000000000005'::jsonb::float4;
1126+
select'12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
1127+
select'12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
1128+
select'12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
1129+
select'12345.0000000000000000000000000000000000000000000005'::jsonb::int8;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp