Horton

Re: pgsql: Add more SQL/JSON constructor functions

jian he <jian.universality@gmail.com>

View thread View on postgr.es
From: jian he <jian.universality@gmail.com>
To: Amit Langote <amitlangote09@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-24T06:24:55Z
Lists: pgsql-hackers 
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));

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