From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter@eisentraut.org>
cc: Amit Langote <amitlangote09@gmail.com>,
        Alvaro Herrera <alvherre@alvh.no-ip.org>,
        pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: Add more SQL/JSON constructor functions
In-reply-to: <50a780ab-f1a4-4c5f-b9a1-88a174cf12af@eisentraut.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>
Comments: In-reply-to Peter Eisentraut <peter@eisentraut.org>
	message dated "Sun, 02 Jun 2024 23:17:06 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2416638.1717389996.1@sss.pgh.pa.us>
Date: Mon, 03 Jun 2024 00:46:36 -0400
Message-ID: <2416639.1717389996@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2416639.1717389996%40sss.pgh.pa.us>
Precedence: bulk

Peter Eisentraut <peter@eisentraut.org> writes:
> On 29.05.24 18:44, Tom Lane wrote:
>> Yeah, I too think this is a cast, and truncation is the spec-defined
>> behavior for casting to varchar with a specific length limit.

> The SQL standard says essentially that the output of json_serialize() is 
> some string that when parsed back in gives you an equivalent JSON value 
> as the input.  That doesn't seem compatible with truncating the output.

Maybe you should take this up with the SQL committee?  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).

> If you want output truncation, you can of course use an actual cast. 
> But it makes sense that the RETURNING clause is separate from that.

Are you trying to say that the RETURNING clause's specified type
isn't the actual output type?  I can't buy that either.

Again, if you think our existing behavior isn't right, I think
it's a problem for the SQL committee not us.

			regards, tom lane