Re: pgsql: Add more SQL/JSON constructor functions

On Mon, Sep 2, 2024 at 4:18 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> > See 0001.
> >

> >
> > See 0002.
> >
> > I'm also attaching 0003 to fix a minor annoyance that JSON_TABLE()
> > columns' default ON ERROR, ON EMPTY behaviors are unnecessarily
> > emitted in the deparsed output when the top-level ON ERROR behavior is
> > ERROR.
> >
> > Will push these on Monday.

v2-0001 looks good to me.

+-- Test JSON_TABLE() column deparsing -- don't emit default ON ERROR / EMPTY
+-- behavior
+EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$'));
+EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text
PATH '$') ERROR ON ERROR);

Are these tests duplicated? appears both in v2-0002 and v2-0003.

0002 output is:
+EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text
PATH '$') ERROR ON ERROR);
+
QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------
+ Table Function Scan on "json_table" (cost=0.01..1.00 rows=100 width=32)
+ Output: a
+ Table Function Call: JSON_TABLE('"a"'::jsonb, '$' AS
json_table_path_0 COLUMNS (a text PATH '$' NULL ON EMPTY NULL ON
ERROR) ERROR ON ERROR)
+(3 rows)

0003 output is:
EXPLAIN VERBOSE SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text
PATH '$') ERROR ON ERROR);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Table Function Scan on "json_table" (cost=0.01..1.00 rows=100 width=32)
Output: a
Table Function Call: JSON_TABLE('"a"'::jsonb, '$' AS
json_table_path_0 COLUMNS (a text PATH '$') ERROR ON ERROR)
(3 rows)

two patches with different output,
overall we should merge 0002 and 0003?

if (jsexpr->on_error &&
jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR &&
(jsexpr->on_error->btype != JSON_BEHAVIOR_NULL || returning_domain))

we can be simplified as
if ( jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR &&
(jsexpr->on_error->btype != JSON_BEHAVIOR_NULL || returning_domain))

since if jsexpr->on_error is NULL, then segfault will appear at the beginning of
ExecInitJsonExpr

+ *
+ * Only add the extra steps for a NULL-valued expression when RETURNING a
+ * domain type to check the constraints, if any.
*/
+ jsestate->jump_error = state->steps_len;
if (jsexpr->on_error &&
- jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR)
+ jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR &&
+ (jsexpr->on_error->btype != JSON_BEHAVIOR_NULL || returning_domain))

+ *
+ * Only add the extra steps for a NULL-valued expression when RETURNING a
+ * domain type to check the constraints, if any.
*/
+ jsestate->jump_empty = state->steps_len;
if (jsexpr->on_empty != NULL &&
- jsexpr->on_empty->btype != JSON_BEHAVIOR_ERROR)
+ jsexpr->on_empty->btype != JSON_BEHAVIOR_ERROR &&
+ (jsexpr->on_empty->btype != JSON_BEHAVIOR_NULL || returning_domain))

I am a little bit confused with the comments.
not sure the "NULL-valued expression" refers to.

i think it is:
implicitly default for ON EMPTY | ERROR clause is NULL (JSON_BEHAVIOR_NULL)
for that situation, we can skip the json coercion process,
but this only applies when the returning type of JsonExpr is not domain,