Hi Peter,
On Wed, Sep 20, 2023 at 4:51 PM Peter Eisentraut <pe...@eisentraut.org>
wrote:
> On 18.09.23 18:55, Chapman Flack wrote:
> > It would make me happy if the message could be changed, and maybe
> > ERRCODE_INVALID_PARAMETER_VALUE also changed, perhaps to one of
> > the JSON-specific ones in the 2203x range.
>
> What is an example of a statement or function call that causes this
> error? Then we can look in the SQL standard for guidance.
>
Thanks for showing interest in this. The issue comes from this situation.
create table tb(a jsonb);
insert into tb select '{"a": "foo", "b": 100000000}';
select cast(a->'a' as numeric) from tb;
ERRCODE_INVALID_PARAMETER_VALUE cannot cast jsonb string to type numeric
the call stack is:
0 in errstart of elog.c:351
1 in errstart_cold of elog.c:333
2 in cannotCastJsonbValue of jsonb.c:2033
3 in jsonb_numeric of jsonb.c:2063
4 in ExecInterpExpr of execExprInterp.c:758
select cast(a->'b' as int2) from tb;
NUMERIC_VALUE_OUT_OF_RANGE smallint out of range
the call stack is:
1 in errstart_cold of elog.c:333
2 in numeric_int2 of numeric.c:4503
3 in DirectFunctionCall1Coll of fmgr.c:785
4 in jsonb_int2 of jsonb.c:2086
There are 2 different errcode involved here and there are two different
functions that play part in it (jsonb_numeric and numeric_int2). and
the error code jsonb_numeric used is improper as well.
The difference is not very huge, but it would be cool if we can make
it better, If something really improves here, it will make the code in [0]
cleaner as well. the bad code in [0]:
+Datum
+jsonb_finish_numeric(PG_FUNCTION_ARGS)
+{
+ JsonbValue *v = (JsonbValue *)PG_GETARG_POINTER(0);
+ Oid final_oid = PG_GETARG_OID(1);
+ if (v->type != jbvNumeric)
+ cannotCastJsonbValue(v->type, format_type_be(final_oid));
+ PG_RETURN_NUMERIC(v->val.numeric);
+}
To match the error message in the older version, I have to input
a {final_oid} argument in jsonb_finish_numeric function which
is not good.
As to how to redesign the error message is a bit confusing to
me, it would be good to see the proposal code as well.
The only concern from me is that the new error from newer
version is not compatible with the older versions, which may matters
matters or doesn't match, I don't know.
[0]
https://www.postgresql.org/message-id/43a988594ac91a63dc4bb49a94303a42%40anastigmatix.net
--
Best Regards
Andy Fan
