Re: Using JSONB directly from application

On 06/24/2018 12:42 PM, Tomas Vondra wrote:

>> Sending raw JSONB to Postgres might also be interesting, but I'd start
>> with receiving.
>>
>> Would implementing raw_jsonb be as trivial as it sounds?  What about
>> usages like SELECT raw_jsonb(col3->'foo'); does the subobject returned
>> by '->' share structure with the containing object, making the
>> conversion to a self-contained JSONB value less direct?
>>
>> Can these conversions be implemented without copying the bytes?
>>
> I don't think you need the function, actually. PostgreSQL protocol
> supports both text and binary mode - in the text mode the server formats
> everything as text before sending it to the client. I guess this is what
> you mean by "convert to json".
>
> But with the extended protocol you (or rather the connection library
> you're using) can specify that the output should be handed in binary,
> i.e. as exact copy of the data. This happens at "bind" phase, see the
> "Bind" message docs here:
>
> https://www.postgresql.org/docs/current/static/protocol-message-formats.html
>
>

jsonb_send just sends 1 followed by the stringified value. If you want
real binary transmission I think you'll need a new output version, by
adjusting jsonb_send and jsonb_recv.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services