Re: pgsql: Add more SQL/JSON constructor functions

On Thu, Jul 18, 2024 at 3:04 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> we still have problem in transformJsonBehavior
>
> currently transformJsonBehavior:
> SELECT JSON_VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 010111 ON ERROR);
> ERROR: cannot cast behavior expression of type text to bit
> LINE 1: ...VALUE(jsonb '1234', '$' RETURNING bit(3) DEFAULT 010111 ON ...
>
> here, 010111 will default to int4, so "cannot cast behavior expression
> of type text to bit"
> is wrong?
> also int4/int8 can be explicitly cast to bit(3), in this case, it
> should return 111.

I think we shouldn't try too hard in the code to "automatically" cast
the DEFAULT expression, especially if that means having to add special
case code for all sorts of source-target-type combinations.

I'm inclined to just give a HINT to the user to cast the DEFAULT
expression by hand, because they *can* do that with the syntax that
exists.

On the other hand, transformJsonBehavior() should handle other
"internal" expressions for which the cast cannot be specified by hand.

> Also, do we want to deal with bit data type's typmod like we did for
> string type in transformJsonBehavior?
> like:
> SELECT JSON_VALUE(jsonb '"111"', '$' RETURNING bit(3) default '1111' on error);
> should return error:
> ERROR: bit string length 2 does not match type bit(3)
> or success
>
> The attached patch makes it return an error, similar to what we did
> for the fixed length string type.

Yeah, that makes sense.

I'm planning to push the attached 2 patches. 0001 is to fix
transformJsonBehavior() for these cases and 0002 to adjust the
behavior of casting the result of JSON_EXISTS() and EXISTS columns to
integer type. I've included the tests in your patch in 0001. I
noticed using cast expression to coerce the boolean constants to
fixed-length types would produce unexpected errors when the planner's
const-simplification calls the cast functions. So in 0001, I've made
that case also use runtime coercion using json_populate_type().

--
Thanks, Amit Langote