public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Amit Langote <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Erik Rijkers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: remaining sql/json patches
Date: Thu, 25 Jan 2024 22:39:30 +0800
Message-ID: <CACJufxG5vymBu=4J0yzMQCtcj51j6FEo=CGQF9Pnp3tzQPp+nw@mail.gmail.com> (raw)
In-Reply-To: <CA+HiwqEw96AwERGSXWLX52oO8p-=BRJUHT6Jc9R1GCty3unZyA@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CA+HiwqGN6DUKAVabqEhQ8e=0593emvJ6P4rQ_8x8N84k=mWq1Q@mail.gmail.com>
<CA+HiwqFctBg7--Om7fFdYUwwfJ5xb1M3L4E3d=-HZ=nXK_ETtA@mail.gmail.com>
<CA+HiwqGpaB7Oyea0qJiAQ_V6-qtfLdCTExcFNDChL_M-80-4uw@mail.gmail.com>
<CA+HiwqEw96AwERGSXWLX52oO8p-=BRJUHT6Jc9R1GCty3unZyA@mail.gmail.com>
On Thu, Jan 25, 2024 at 7:54 PM Amit Langote <[email protected]> wrote:
>
> >
> > 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.
>
Hi, I reconciled with these cases.
What bugs me now is the first query of the following 4 cases (for comparison).
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3) omit quotes);
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3) keep quotes);
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text omit quotes);
SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text keep quotes);
I did some minor refactoring on the function coerceJsonFuncExprOutput.
it will make the following queries return null instead of error. NULL
is the return of json_value.
SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING int2);
SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING int4);
SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING int8);
SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING bool);
SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING numeric);
SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING real);
SELECT JSON_QUERY(jsonb '"123"', '$' RETURNING float8);
Attachments:
[application/octet-stream] v1-0001-minor-refactor-coerceJsonFuncExprOutput.no-cfbot (1.8K, ../CACJufxG5vymBu=4J0yzMQCtcj51j6FEo=CGQF9Pnp3tzQPp+nw@mail.gmail.com/2-v1-0001-minor-refactor-coerceJsonFuncExprOutput.no-cfbot)
download
view thread (27+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: remaining sql/json patches
In-Reply-To: <CACJufxG5vymBu=4J0yzMQCtcj51j6FEo=CGQF9Pnp3tzQPp+nw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox