From 2dcc56b0e5ac12ae4b07d421c0ed8515568b06e6 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 24 Nov 2025 14:26:53 +0800
Subject: [PATCH v16 17/23] 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 | 74 +++++++++++++++++++++++++++--------
 1 file changed, 58 insertions(+), 16 deletions(-)

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index c4fe6e00dcd..1600204fa4a 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1815,7 +1815,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
 	{
@@ -1836,7 +1836,7 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype)
 
 	for (i = 0; i < lengthof(messages); i++)
 		if (messages[i].type == type)
-			ereport(ERROR,
+			ereturn(escontext,,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 					 errmsg(messages[i].msg, sqltype)));
 
@@ -1851,7 +1851,10 @@ 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)
 	{
@@ -1860,7 +1863,10 @@ 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);
 
@@ -1875,7 +1881,10 @@ 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)
 	{
@@ -1884,7 +1893,10 @@ 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
@@ -1905,7 +1917,10 @@ 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)
 	{
@@ -1914,7 +1929,10 @@ 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));
@@ -1932,7 +1950,10 @@ 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)
 	{
@@ -1941,7 +1962,10 @@ 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));
@@ -1959,7 +1983,10 @@ 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)
 	{
@@ -1968,7 +1995,10 @@ 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));
@@ -1986,7 +2016,10 @@ 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)
 	{
@@ -1995,7 +2028,10 @@ 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));
@@ -2013,7 +2049,10 @@ 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)
 	{
@@ -2022,7 +2061,10 @@ 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

