v42-0001-minor-refactor-SQL-JSON-query-functions-based.no-cfbot

From 94725e35d0c36c19c3d729294e264b410232ab08 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 7 Mar 2024 21:38:30 +0800
Subject: [PATCH v42 1/2] minor refactor SQL/JSON query functions based on v24.

---
 src/backend/executor/execExprInterp.c         | 25 +++++++++++++++++++
 src/backend/parser/parse_expr.c               | 19 ++++++++++++++
 .../regress/expected/sqljson_queryfuncs.out   |  2 +-
 3 files changed, 45 insertions(+), 1 deletion(-)

diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index dad424ba..fc36b086 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -4325,6 +4325,31 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
 						*op->resvalue = JsonbPGetDatum(JsonbValueToJsonb(jbv));
 						*op->resnull = false;
 					}
+					else if (jbv->type == jbvNumeric)
+					{
+						switch (jexpr->returning->typid)
+						{
+							/*
+							* we need to handle these cases seperately.
+							* because cast from numeric to int2, int4,
+							* int8, float4, float8 cannot handle error
+							* softly. See coerceJsonFuncExprOutput.
+							*/
+							case  INT2OID:
+							case  INT4OID:
+							case  INT8OID:
+							case  FLOAT4OID:
+							case  FLOAT8OID:
+								*op->resvalue = JsonbPGetDatum(JsonbValueToJsonb(jbv));
+								*op->resnull = false;
+								break;
+							default:
+								ExecPrepareJsonItemCoercion(jbv, jsestate, throw_error,
+															&jump_eval_coercion,
+															op->resvalue, op->resnull);
+								break;
+						}
+					}
 					else
 					{
 						/*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 6557b07f..f4d5ef53 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4535,6 +4535,25 @@ coerceJsonFuncExprOutput(ParseState *pstate, JsonExpr *jsexpr)
 				break;
 		}
 	}
+	/*
+	 * the JsonbValue returned by JsonPathValue maybe a jbvType is jbvNumeric
+	 * in that case, we need to use JsonCoercion node to handle error softly.
+	 *
+	*/
+	if (jsexpr->op == JSON_VALUE_OP)
+	{
+		switch (returning->typid)
+		{
+			case INT2OID:
+			case INT4OID:
+			case INT8OID:
+			case FLOAT4OID:
+			case FLOAT8OID:
+				return (Node *) makeJsonCoercion(returning, omit_quotes, NULL);
+			default:
+				break;
+		}
+	}
 
 	default_typid = JsonFuncExprDefaultReturnType(jsexpr);
 	default_typmod = -1;
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index f5b57465..23054d9a 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -213,7 +213,7 @@ SELECT JSON_VALUE(jsonb '1.23', '$');
 SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING int);
  json_value 
 ------------
-          1
+           
 (1 row)
 
 SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
-- 
2.34.1