v8-0017-error-safe-for-casting-jsonb-to-other-types-per-pg_cast.patch
text/x-patch
Filename: v8-0017-error-safe-for-casting-jsonb-to-other-types-per-pg_cast.patch
Type: text/x-patch
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v8-0017
Subject: error safe for casting jsonb to other types per pg_cast
| File | + | − |
|---|---|---|
| src/backend/utils/adt/jsonb.c | 75 | 16 |
From e015c06c2b620864c4203393d388a6b1091cbbf3 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 9 Oct 2025 18:58:40 +0800
Subject: [PATCH v8 17/20] error safe for casting jsonb to other types per
pg_cast
select castsource::regtype, casttarget::regtype, castfunc,
castcontext,castmethod, pp.prosrc, pp.proname from pg_cast pc join pg_proc pp on
pp.oid = pc.castfunc and pc.castfunc > 0
and castsource::regtype = 'jsonb'::regtype
order by castsource::regtype;
castsource | casttarget | castfunc | castcontext | castmethod | prosrc | proname
------------+------------------+----------+-------------+------------+---------------+---------
jsonb | boolean | 3556 | e | f | jsonb_bool | bool
jsonb | numeric | 3449 | e | f | jsonb_numeric | numeric
jsonb | smallint | 3450 | e | f | jsonb_int2 | int2
jsonb | integer | 3451 | e | f | jsonb_int4 | int4
jsonb | bigint | 3452 | e | f | jsonb_int8 | int8
jsonb | real | 3453 | e | f | jsonb_float4 | float4
jsonb | double precision | 2580 | e | f | jsonb_float8 | float8
(7 rows)
discussion: https://postgr.es/m/CADkLM=fv1JfY4Ufa-jcwwNbjQixNViskQ8jZu3Tz_p656i_4hQ@mail.gmail.com
---
src/backend/utils/adt/jsonb.c | 91 +++++++++++++++++++++++++++++------
1 file changed, 75 insertions(+), 16 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index da94d424d61..1ff00f72f78 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -2005,7 +2005,7 @@ JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res)
* Emit correct, translatable cast error message
*/
static void
-cannotCastJsonbValue(enum jbvType type, const char *sqltype)
+cannotCastJsonbValue(enum jbvType type, const char *sqltype, Node *escontext)
{
static const struct
{
@@ -2026,9 +2026,12 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype)
for (i = 0; i < lengthof(messages); i++)
if (messages[i].type == type)
- ereport(ERROR,
+ {
+ errsave(escontext,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg(messages[i].msg, sqltype)));
+ return;
+ }
/* should be unreachable */
elog(ERROR, "unknown jsonb type: %d", (int) type);
@@ -2041,7 +2044,11 @@ jsonb_bool(PG_FUNCTION_ARGS)
JsonbValue v;
if (!JsonbExtractScalar(&in->root, &v))
- cannotCastJsonbValue(v.type, "boolean");
+ {
+ cannotCastJsonbValue(v.type, "boolean", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
if (v.type == jbvNull)
{
@@ -2050,7 +2057,11 @@ jsonb_bool(PG_FUNCTION_ARGS)
}
if (v.type != jbvBool)
- cannotCastJsonbValue(v.type, "boolean");
+ {
+ cannotCastJsonbValue(v.type, "boolean", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
PG_FREE_IF_COPY(in, 0);
@@ -2065,7 +2076,11 @@ jsonb_numeric(PG_FUNCTION_ARGS)
Numeric retValue;
if (!JsonbExtractScalar(&in->root, &v))
- cannotCastJsonbValue(v.type, "numeric");
+ {
+ cannotCastJsonbValue(v.type, "numeric", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
if (v.type == jbvNull)
{
@@ -2074,7 +2089,11 @@ jsonb_numeric(PG_FUNCTION_ARGS)
}
if (v.type != jbvNumeric)
- cannotCastJsonbValue(v.type, "numeric");
+ {
+ cannotCastJsonbValue(v.type, "numeric", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
/*
* v.val.numeric points into jsonb body, so we need to make a copy to
@@ -2095,7 +2114,11 @@ jsonb_int2(PG_FUNCTION_ARGS)
Datum retValue;
if (!JsonbExtractScalar(&in->root, &v))
- cannotCastJsonbValue(v.type, "smallint");
+ {
+ cannotCastJsonbValue(v.type, "smallint", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
if (v.type == jbvNull)
{
@@ -2104,7 +2127,11 @@ jsonb_int2(PG_FUNCTION_ARGS)
}
if (v.type != jbvNumeric)
- cannotCastJsonbValue(v.type, "smallint");
+ {
+ cannotCastJsonbValue(v.type, "smallint", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
retValue = DirectFunctionCall1(numeric_int2,
NumericGetDatum(v.val.numeric));
@@ -2122,7 +2149,11 @@ jsonb_int4(PG_FUNCTION_ARGS)
Datum retValue;
if (!JsonbExtractScalar(&in->root, &v))
- cannotCastJsonbValue(v.type, "integer");
+ {
+ cannotCastJsonbValue(v.type, "integer", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
if (v.type == jbvNull)
{
@@ -2131,7 +2162,11 @@ jsonb_int4(PG_FUNCTION_ARGS)
}
if (v.type != jbvNumeric)
- cannotCastJsonbValue(v.type, "integer");
+ {
+ cannotCastJsonbValue(v.type, "integer", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
retValue = DirectFunctionCall1(numeric_int4,
NumericGetDatum(v.val.numeric));
@@ -2149,7 +2184,11 @@ jsonb_int8(PG_FUNCTION_ARGS)
Datum retValue;
if (!JsonbExtractScalar(&in->root, &v))
- cannotCastJsonbValue(v.type, "bigint");
+ {
+ cannotCastJsonbValue(v.type, "bigint", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
if (v.type == jbvNull)
{
@@ -2158,7 +2197,11 @@ jsonb_int8(PG_FUNCTION_ARGS)
}
if (v.type != jbvNumeric)
- cannotCastJsonbValue(v.type, "bigint");
+ {
+ cannotCastJsonbValue(v.type, "bigint", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
retValue = DirectFunctionCall1(numeric_int8,
NumericGetDatum(v.val.numeric));
@@ -2176,7 +2219,11 @@ jsonb_float4(PG_FUNCTION_ARGS)
Datum retValue;
if (!JsonbExtractScalar(&in->root, &v))
- cannotCastJsonbValue(v.type, "real");
+ {
+ cannotCastJsonbValue(v.type, "real", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
if (v.type == jbvNull)
{
@@ -2185,7 +2232,11 @@ jsonb_float4(PG_FUNCTION_ARGS)
}
if (v.type != jbvNumeric)
- cannotCastJsonbValue(v.type, "real");
+ {
+ cannotCastJsonbValue(v.type, "real", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
retValue = DirectFunctionCall1(numeric_float4,
NumericGetDatum(v.val.numeric));
@@ -2203,7 +2254,11 @@ jsonb_float8(PG_FUNCTION_ARGS)
Datum retValue;
if (!JsonbExtractScalar(&in->root, &v))
- cannotCastJsonbValue(v.type, "double precision");
+ {
+ cannotCastJsonbValue(v.type, "double precision", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
if (v.type == jbvNull)
{
@@ -2212,7 +2267,11 @@ jsonb_float8(PG_FUNCTION_ARGS)
}
if (v.type != jbvNumeric)
- cannotCastJsonbValue(v.type, "double precision");
+ {
+ cannotCastJsonbValue(v.type, "double precision", fcinfo->context);
+
+ PG_RETURN_NULL();
+ }
retValue = DirectFunctionCall1(numeric_float8,
NumericGetDatum(v.val.numeric));
--
2.34.1