Re: pgsql: Add more SQL/JSON constructor functions

On Tue, Jul 23, 2024 at 8:52 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> In the attached patch, I've also taken care of the problem mentioned
> in your latest email -- the solution I've chosen is not to produce the
> error when ERROR ON ERROR is specified but to use runtime coercion
> also for the jsonb type or any type that is not integer. Also fixed
> the typos.
>
> Thanks for your attention!
>

COLUMNS (col_name jsonb EXISTS PATH 'pah_expression') inconsistency
seems resolved.
I also tested the domain over jsonb, it works.

transformJsonFuncExpr we have:
case JSON_QUERY_OP:
if (jsexpr->returning->typid != JSONBOID || jsexpr->omit_quotes)
jsexpr->use_json_coercion = true;

case JSON_VALUE_OP:
if (jsexpr->returning->typid != TEXTOID)
{
if (get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN &&
DomainHasConstraints(jsexpr->returning->typid))
jsexpr->use_json_coercion = true;
else
jsexpr->use_io_coercion = true;
}

JSONBOID won't be a domain. for domain type, json_value, json_query
will use jsexpr->use_json_coercion.
jsexpr->use_json_coercion can handle whether the domain has constraints or not.

so i don't know the purpose of following code in ExecInitJsonExpr
if (get_typtype(jsexpr->returning->typid) == TYPTYPE_DOMAIN &&
DomainHasConstraints(jsexpr->returning->typid))
{
Assert(jsexpr->use_json_coercion);
scratch->opcode = EEOP_JUMP;
scratch->d.jump.jumpdone = state->steps_len + 1;
ExprEvalPushStep(state, scratch);
}

json_table exits works fine with int4, not domain over int4. The
following are test suites.

drop domain if exists dint4, dint4_1,dint4_0;
create domain dint4 as int;
create domain dint4_1 as int check ( value <> 1 );
create domain dint4_0 as int check ( value <> 0 );
SELECT a, a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4
EXISTS PATH '$.a' ));
SELECT a, a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4
EXISTS PATH '$.a' false ON ERROR));
SELECT a, a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4
EXISTS PATH '$.a' ERROR ON ERROR));
SELECT a, a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4_0
EXISTS PATH '$.a'));
SELECT a, a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4_0
EXISTS PATH '$'));
SELECT a,a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4_1
EXISTS PATH '$'));
SELECT a,a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4_1
EXISTS PATH '$.a'));
SELECT a,a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4_1
EXISTS PATH '$.a' ERROR ON ERROR));