Re: remaining sql/json patches

Hello,

On Thu, Jul 20, 2023 at 10:35 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> On Tue, Jul 18, 2023 at 5:11 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > > Op 7/17/23 om 07:00 schreef jian he:
> > > > hi.
> > > > seems there is no explanation about, json_api_common_syntax in
> > > > functions-json.html
> > > >
> > > > I can get json_query full synopsis from functions-json.html as follows:
> > > > json_query ( context_item, path_expression [ PASSING { value AS
> > > > varname } [, ...]] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8
> > > > ] ] ] [ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ]
> > > > WRAPPER ] [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ] [ { ERROR |
> > > > NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression } ON EMPTY ]
> > > > [ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression }
> > > > ON ERROR ])
> > > >
> > > > seems doesn't have a full synopsis for json_table? only partial one
> > > > by one explanation.
> >
> > I looked through the history of the docs portion of the patch and it
> > looks like the synopsis for JSON_TABLE(...) used to be there but was
> > taken out during one of the doc reworks [1].
> >
> > I've added it back in the patch as I agree that it would help to have
> > it. Though, I am not totally sure where I've put it is the right
> > place for it. JSON_TABLE() is a beast that won't fit into the table
> > that JSON_QUERY() et al are in, so maybe that's how it will have to
> > be? I have no better idea.
>
> attached screenshot render json_table syntax almost plain html. It looks fine.

Thanks for checking.

> based on syntax, then I am kind of confused with following 2 cases:
> --1
> SELECT * FROM JSON_TABLE(jsonb '1', '$'
> COLUMNS (a int PATH 'strict $.a' default 1 ON EMPTY default 2 on error)
> ERROR ON ERROR) jt;
>
> --2
> SELECT * FROM JSON_TABLE(jsonb '1', '$'
> COLUMNS (a int PATH 'strict $.a' default 1 ON EMPTY default 2 on error)) jt;
>
> the first one should yield syntax error?

No. Actually, the synopsis missed the optional ON ERROR clause that
can appear after COLUMNS(...). Will fix it.

--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com