Re: pgsql: Add more SQL/JSON constructor functions

On Tue, Jul 23, 2024 at 8:52 PM Amit Langote <amitlangote09@gmail.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));