Re: Extract numeric filed in JSONB more effectively

On 2023-08-18 15:08, Chapman Flack wrote:
> But I don't know that adding relabel nodes wouldn't still be
> the civilized thing to do.

Interestingly, when I relabel both places, like this:

Oid targetOid = fexpr->funcresulttype;
Const *target = makeConst(
OIDOID, -1, InvalidOid, sizeof(Oid),
ObjectIdGetDatum(targetOid), false, true);
RelabelType *rTarget = makeRelabelType((Expr *)target,
INTERNALOID, -1, InvalidOid, COERCE_IMPLICIT_CAST);
fexpr->funcid = new_func_id;
fexpr->args = opexpr->args;
fexpr->args = list_insert_nth(fexpr->args, 0, rTarget);
expr = (Expr *)makeRelabelType((Expr *)fexpr,
targetOid, -1, InvalidOid, COERCE_IMPLICIT_CAST);
}
PG_RETURN_POINTER(expr);

EXPLAIN looks like this:

Seq Scan on pg_temp.test_jsonb
Output: jsonb_array_element_type(('23'::oid)::internal, test_json,
0), (test_json -> 0)
Filter: (test_jsonb.json_type = 'scalarint'::text)

With COERCE_IMPLICIT_CAST both places, the relabeling of the
function result is invisible, but the relabeling of the argument
is visible.

With the second one changed to COERCE_EXPLICIT_CAST:

Seq Scan on pg_temp.test_jsonb
Output: (jsonb_array_element_type(('23'::oid)::internal, test_json,
0))::integer, (test_json -> 0)
Filter: (test_jsonb.json_type = 'scalarint'::text)

then both relabelings are visible.

I'm not sure whether one way is better than the other, or whether
it is even important to add the relabel nodes at all, as nothing
raises an error without them. As a matter of taste, it seems like
a good idea though.

Regards,
-Chap