Re: pgsql: Add more SQL/JSON constructor functions

On 29.05.24 18:44, Tom Lane wrote:
> Amit Langote <amitlangote09(at)gmail(dot)com> writes:
>> On Mon, May 27, 2024 at 7:10 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>>> On 2024-May-27, Alvaro Herrera wrote:
>>> I just noticed this behavior, which looks like a bug to me:
>>>
>>> select json_serialize('{"a":1, "a":2}' returning varchar(5));
>>> json_serialize
>>> ────────────────
>>> {"a":
>>>
>>> I think this function should throw an error if the destination type
>>> doesn't have room for the output json. Otherwise, what good is the
>>> serialization function?
>
>> This behavior comes from using COERCE_EXPLICIT_CAST when creating the
>> coercion expression to convert json_*() functions' argument to the
>> RETURNING type.
>
> Yeah, I too think this is a cast, and truncation is the spec-defined
> behavior for casting to varchar with a specific length limit. I see
> little reason that this should work differently from
>
> select json_serialize('{"a":1, "a":2}' returning text)::varchar(5);
> json_serialize
> ----------------
> {"a":
> (1 row)

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.

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.