Feedback-ID: ie0a040ee:Fastmail
Message-ID: <65d2544e-4a11-448d-9683-7bce0d43565d@eisentraut.org>
Date: Mon, 3 Jun 2024 11:15:37 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pgsql: Add more SQL/JSON constructor functions
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Amit Langote <amitlangote09@gmail.com>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>, pgsql-hackers@lists.postgresql.org
References: <202405271313.qkl63gffldmx@alvherre.pgsql>
 <202405271326.5a5rprki64aw@alvherre.pgsql>
 <CA+HiwqE+y_HmGJpKk2_w+CUzmAw=Z5zd6j3_LmCPiGpFyN1gzQ@mail.gmail.com>
 <3189.1717001075@sss.pgh.pa.us>
 <50a780ab-f1a4-4c5f-b9a1-88a174cf12af@eisentraut.org>
 <2416639.1717389996@sss.pgh.pa.us>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <2416639.1717389996@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/65d2544e-4a11-448d-9683-7bce0d43565d%40eisentraut.org>
Precedence: bulk

On 02.06.24 21:46, Tom Lane wrote:
> If you don't
> like our current behavior, then either you have to say that RETURNING
> with a length-limited target type is illegal (which is problematic
> for the spec, since they have no such type) or that the cast behaves
> like an implicit cast, with errors for overlength input (which I find
> to be an unintuitive definition for a construct that names the target
> type explicitly).

It asks for the latter behavior, essentially (but it's not defined in 
terms of casts).  It says:

"""
ii) Let JV be an implementation-dependent (UV097) value of type TT and 
encoding ENC such that these two conditions hold:

1) JV is a JSON text.

2) When the General Rules of Subclause 9.42, “Parsing JSON text”, are 
applied with JV as JSON TEXT, FO as FORMAT OPTION, and WITHOUT UNIQUE 
KEYS as UNIQUENESS CONSTRAINT; let CST be the STATUS and let CSJI be the 
SQL/JSON ITEM returned from the application of those General Rules, CST 
is successful completion (00000) and CSJI is an SQL/JSON item that is 
equivalent to SJI.

If there is no such JV, then let ST be the exception condition: data 
exception — invalid JSON text (22032).

iii) If JV is longer than the length or maximum length of TT, then an 
exception condition is raised: data exception — string data, right 
truncation (22001).
"""

Oracle also behaves accordingly:

SQL> select json_serialize('{"a":1, "a":2}' returning varchar2(20)) from 
dual;

JSON_SERIALIZE('{"A"
--------------------
{"a":1,"a":2}

SQL> select json_serialize('{"a":1, "a":2}' returning varchar2(5)) from 
dual;
select json_serialize('{"a":1, "a":2}' returning varchar2(5)) from dual
                                                                    *
ERROR at line 1:
ORA-40478: output value too large (maximum: 5)
JZN-00018: Input to serializer is too large
Help: https://docs.oracle.com/error-help/db/ora-40478/


As opposed to:

SQL> select cast(json_serialize('{"a":1, "a":2}') as varchar2(5)) from dual;

CAST(
-----
{"a":