Re: remaining sql/json patches - Mailing list pgsql-hackers

From Amit Langote
Subject Re: remaining sql/json patches
Date
Msg-id CA+HiwqEw96AwERGSXWLX52oO8p-=BRJUHT6Jc9R1GCty3unZyA@mail.gmail.com
Whole thread Raw
In response to Re: remaining sql/json patches  (Amit Langote <amitlangote09@gmail.com>)
Responses Re: remaining sql/json patches
List pgsql-hackers
On Thu, Jan 25, 2024 at 6:09 PM Amit Langote <amitlangote09@gmail.com> wrote:
> On Wed, Jan 24, 2024 at 10:11 PM Amit Langote <amitlangote09@gmail.com> wrote:
> > I still need to take a look at your other report regarding typmod but
> > I'm out of energy today.
>
> The attached updated patch should address one of the concerns --
> JSON_QUERY() should now work appropriately with RETURNING type with
> typmod whether or  OMIT QUOTES is specified.
>
> But I wasn't able to address the problems with RETURNING
> record_type_with_typmod, that is, the following example you shared
> upthread:
>
> create domain char3_domain_not_null as char(3) NOT NULL;
> create domain hello as text not null check (value = 'hello');
> create domain int42 as int check (value = 42);
> create type comp_domain_with_typmod AS (a char3_domain_not_null, b int42);
> select json_value(jsonb'{"rec": "(abcd,42)"}', '$.rec' returning
> comp_domain_with_typmod);
>  json_value
> ------------
>
> (1 row)
>
> select json_value(jsonb'{"rec": "(abcd,42)"}', '$.rec' returning
> comp_domain_with_typmod error on error);
> ERROR:  value too long for type character(3)
>
> select json_value(jsonb'{"rec": "abcd"}', '$.rec' returning
> char3_domain_not_null error on error);
>  json_value
> ------------
>  abc
> (1 row)
>
> The problem with returning comp_domain_with_typmod from json_value()
> seems to be that it's using a text-to-record CoerceViaIO expression
> picked from JsonExpr.item_coercions, which behaves differently than
> the expression tree that the following uses:
>
> select ('abcd', 42)::comp_domain_with_typmod;
>    row
> ----------
>  (abc,42)
> (1 row)

Oh, it hadn't occurred to me to check what trying to coerce a "string"
containing the record literal would do:

select '(''abcd'', 42)'::comp_domain_with_typmod;
ERROR:  value too long for type character(3)
LINE 1: select '(''abcd'', 42)'::comp_domain_with_typmod;

which is the same thing as what the JSON_QUERY() and JSON_VALUE() are
running into.  So, it might be fair to think that the error is not a
limitation of the SQL/JSON patch but an underlying behavior that it
has to accept as is.

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



pgsql-hackers by date:

Previous
From: Bertrand Drouvot
Date:
Subject: Re: Synchronizing slots from primary to standby
Next
From: Aleksander Alekseev
Date:
Subject: Re: UUID v7