Re: pgsql: Add more SQL/JSON constructor functions

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":