Horton

Re: pgsql: Add more SQL/JSON constructor functions

Amit Langote <amitlangote09@gmail.com>

View thread View on postgr.es
From: Amit Langote <amitlangote09@gmail.com>
To: jian he <jian.universality@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-07-22T08:46:18Z
Lists: pgsql-hackers

Attachments

On Thu, Jul 18, 2024 at 3:04 PM jian he <jian.universality@gmail.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

Commits

  1. SQL/JSON: Avoid initializing unnecessary ON ERROR / ON EMPTY steps

  2. SQL/JSON: Fix default ON ERROR behavior for JSON_TABLE

  3. SQL/JSON: Fix JSON_TABLE() column deparsing

  4. Update comment about ExprState.escontext

  5. SQL/JSON: Fix casting for integer EXISTS columns in JSON_TABLE

  6. SQL/JSON: Some fixes to JsonBehavior expression casting

  7. SQL/JSON: Remove useless code in ExecInitJsonExpr()

  8. SQL/JSON: Respect OMIT QUOTES when RETURNING domains over jsonb

  9. SQL/JSON: Improve error-handling of JsonBehavior expressions

  10. SQL/JSON: Fix error-handling of some JsonBehavior expressions

  11. SQL/JSON: Rethink c2d93c3802b

  12. SQL/JSON: Always coerce JsonExpr result at runtime

  13. SQL/JSON: Fix coercion of constructor outputs to types with typmod