Re: pgsql: Add more SQL/JSON constructor functions

On Wed, Jun 26, 2024 at 8:39 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> >
> > The RETURNING variant giving an error is what the standard asks us to
> > do apparently. I read Tom's last message on this thread as agreeing
> > to that, even though hesitantly. He can correct me if I got that
> > wrong.
> >
> > > your patch will make domain and char(n) behavior inconsistent.
> > > create domain char2 as char(2);
> > > SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char2 ERROR ON ERROR);
> > > SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2) ERROR ON ERROR);
> > >
> > >
> > > another example:
> > > SELECT JSON_query(jsonb '"aaa"', '$' RETURNING char(2) keep quotes
> > > default '"aaa"'::jsonb ON ERROR);
> > > same value (jsonb "aaa") error on error will yield error,
> > > but `default expression on error` can coerce the value to char(2),
> > > which looks a little bit inconsistent, I think.
> >
> > Interesting examples, thanks for sharing.
> >
> > Attached updated version should take into account that typmod may be
> > hiding under domains. Please test.
>

SELECT JSON_VALUE(jsonb '111', '$' RETURNING queryfuncs_char2 default
'13' on error);
return
ERROR: value too long for type character(2)
should return 13

I found out the source of the problem is in coerceJsonExprOutput
/*
* Use cast expression for domain types; we need CoerceToDomain here.
*/
if (get_typtype(returning->typid) != TYPTYPE_DOMAIN)
{
jsexpr->use_io_coercion = true;
return;
}

>
> I'd like to push this one tomorrow, barring objections.
>

Currently the latest patch available cannot be `git apply` cleanly.

@@ -464,3 +466,9 @@ SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xyz);
SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR);
SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR);
SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR);
+
+-- Test implicit coercion domain over fixed-legth type specified in RETURNING
+CREATE DOMAIN queryfuncs_char2 AS char(2) CHECK (VALUE NOT IN ('12'));
+SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '12', '$' RETURNING queryfuncs_char2 ERROR ON ERROR);

cannot found `SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR);` in
https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/sql/sqljson_queryfuncs.sql