Re: More new SQL/JSON item methods

Hi Jeevan,

I think these are in reasonably good shape, but there are a few things that concern me:

andrew@~=# select jsonb_path_query_array('[1.2]', '$[*].bigint()');
ERROR:  numeric argument of jsonpath item method .bigint() is out of range for type bigint

I'm ok with this being an error, but I think the error message is wrong. It should be the "invalid input" message.

andrew@~=# select jsonb_path_query_array('[1.0]', '$[*].bigint()');
ERROR:  numeric argument of jsonpath item method .bigint() is out of range for type bigint

Should we trim trailing dot+zeros from numeric values before trying to convert to bigint/int? If not, this too should be an "invalid input" case.

We have the same issue with integer conversion and need a fix.

Unfortunately, I was using int8in() for the conversion of numeric values. We should be using numeric_int8() instead. However, there is no opt_error version of the same.

So, I have introduced a numeric_int8_opt_error() version just like we have one for int4, i.e. numeric_int4_opt_error(), to suppress the error. These changes are in the 0001 patch. (All other patch numbers are now increased by 1)

I have used this new function to fix this reported issue and used numeric_int4_opt_error() for integer conversion.

andrew@~=# select jsonb_path_query_array('[1.0]', '$[*].boolean()');
ERROR:  numeric argument of jsonpath item method .boolean() is out of range for type boolean

It seems odd that any non-zero integer is true but not any non-zero numeric. Is that in the spec? If not I'd avoid trying to convert it to an integer first, and just check for infinity/nan before looking to see if it's zero.

The code for integer() and bigint() seems a bit duplicative, but I'm not sure there's a clean way of avoiding that.

The items for datetime types and string look OK.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com