Re: finish TODOs in to_json_is_immutable, to_jsonb_is_immutable also add tests on it

On 2026-03-10 Tu 2:31 AM, jian he wrote:
> On Fri, Mar 6, 2026 at 6:09 AM Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> Here's a rework of this patch. It preserves the original signature of
>> to_json{b}_is_immutable, and fixes some code duplication. It also uses
>> the typecache to get composite info instead of calling relation_open,
>> supports MultiRange types, and exits early if we made a recursive call
>> (no need for json_categorize_type etc. in these cases).
>>
>
> In json_categorize_type, we have:
> case INT2OID:
> case INT4OID:
> case INT8OID:
> case FLOAT4OID:
> case FLOAT8OID:
> case NUMERICOID:
> getTypeOutputInfo(typoid, outfuncoid, &typisvarlena);
> *tcategory = JSONTYPE_NUMERIC;
>
> select proname, provolatile from pg_Proc where proname in ('int8out',
> 'int2out', 'int4out', 'float4out', 'float8out', 'numeric_out');
>
> proname | provolatile
> -------------+-------------
> int2out | i
> int4out | i
> float4out | i
> float8out | i
> int8out | i
> numeric_out | i
> (6 rows)
>
> Therefore for JSONTYPE_NUMERIC, has_mutable in json_check_mutability
> will be false.
> I slightly changed the `switch (tcategory)` handling in `json_check_mutability`.
> Make the handling order the same as JsonTypeCategory.
>
>
> Cosmetic change in src/test/regress/sql/sqljson.sql
> --error
> To
> -- error
>

OK, here's a v7.

. added a break in the loop if we found something mutable

. added test for JSON generated columns (was present for JSONB
expression indexes but missing for JSON).

. added test block demonstrating that range_int (subtype=int) and
multirange_int are now correctly treated as immutable, allowing
expression indexes via json_array()
  and json_object()

cheers

andrew

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