Re: remaining sql/json patches
От | jian he |
---|---|
Тема | Re: remaining sql/json patches |
Дата | |
Msg-id | CACJufxF-afCvfjJ=gFar+59bQVaFsMxr3RNyCOnJQJ26ieR3pw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: remaining sql/json patches (jian he <jian.universality@gmail.com>) |
Ответы |
Re: remaining sql/json patches
(Amit Langote <amitlangote09@gmail.com>)
|
Список | pgsql-hackers |
On Tue, Mar 26, 2024 at 6:16 PM jian he <jian.universality@gmail.com> wrote: > > On Fri, Mar 22, 2024 at 12:08 AM Amit Langote <amitlangote09@gmail.com> wrote: > > > > On Wed, Mar 20, 2024 at 9:53 PM Amit Langote <amitlangote09@gmail.com> wrote: > > > I'll push 0001 tomorrow. > > > > Pushed that one. Here's the remaining JSON_TABLE() patch. > > hi. I don't fully understand all the code in json_table patch. maybe we can split it into several patches, like: * no nested json_table_column. * nested json_table_column, with PLAN DEFAULT * nested json_table_column, with PLAN ( json_table_plan ) i can understand the "no nested json_table_column" part, which seems to be how oracle[1] implemented it. I think we can make the "no nested json_table_column" part into v17. i am not sure about other complex parts. lack of comment, makes it kind of hard to fully understand. [1] https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/img_text/json_table.html +/* Reset context item of a scan, execute JSON path and reset a scan */ +static void +JsonTableResetContextItem(JsonTableScanState *scan, Datum item) +{ + MemoryContext oldcxt; + JsonPathExecResult res; + Jsonb *js = (Jsonb *) DatumGetJsonbP(item); + + JsonValueListClear(&scan->found); + + MemoryContextResetOnly(scan->mcxt); + + oldcxt = MemoryContextSwitchTo(scan->mcxt); + + res = executeJsonPath(scan->path, scan->args, + GetJsonPathVar, CountJsonPathVars, + js, scan->errorOnError, &scan->found, + false /* FIXME */ ); + + MemoryContextSwitchTo(oldcxt); + + if (jperIsError(res)) + { + Assert(!scan->errorOnError); + JsonValueListClear(&scan->found); /* EMPTY ON ERROR case */ + } + + JsonTableRescan(scan); +} "FIXME". set the last argument in executeJsonPath to true also works as expected. also there is no test related to the "FIXME" i am not 100% sure about the "FIXME". see demo (after set the executeJsonPath's "useTz" argument to true). create table ss(js jsonb); INSERT into ss select '{"a": "2018-02-21 12:34:56 +10"}'; INSERT into ss select '{"b": "2018-02-21 12:34:56 "}'; PREPARE q2 as SELECT jt.* FROM ss, JSON_TABLE(js, '$.a.datetime()' COLUMNS ("int7" timestamptz PATH '$')) jt; PREPARE qb as SELECT jt.* FROM ss, JSON_TABLE(js, '$.b.datetime()' COLUMNS ("tstz" timestamptz PATH '$')) jt; PREPARE q3 as SELECT jt.* FROM ss, JSON_TABLE(js, '$.a.datetime()' COLUMNS ("ts" timestamp PATH '$')) jt; begin; set time zone +10; EXECUTE q2; set time zone -10; EXECUTE q2; rollback; begin; set time zone +10; SELECT JSON_VALUE(js, '$.a' returning timestamptz) from ss; set time zone -10; SELECT JSON_VALUE(js, '$.a' returning timestamptz) from ss; rollback; --------------------------------------------------------------------- begin; set time zone +10; EXECUTE qb; set time zone -10; EXECUTE qb; rollback; begin; set time zone +10; SELECT JSON_VALUE(js, '$.b' returning timestamptz) from ss; set time zone -10; SELECT JSON_VALUE(js, '$.b' returning timestamptz) from ss; rollback; --------------------------------------------------------------------- begin; set time zone +10; EXECUTE q3; set time zone -10; EXECUTE q3; rollback; begin; set time zone +10; SELECT JSON_VALUE(js, '$.b' returning timestamp) from ss; set time zone -10; SELECT JSON_VALUE(js, '$.b' returning timestamp) from ss; rollback;
В списке pgsql-hackers по дате отправления: