Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQYRz-0006pX-FF for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Aug 2022 18:16:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oQYRy-0007cl-8z for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Aug 2022 18:16:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQYRx-0007cc-QJ for pgsql-hackers@lists.postgresql.org; Tue, 23 Aug 2022 18:16:29 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oQYRu-00039q-Mu for pgsql-hackers@lists.postgresql.org; Tue, 23 Aug 2022 18:16:29 +0000 Received: from [192.168.8.199] (unknown [93.174.131.141]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 661F821C3093; Tue, 23 Aug 2022 21:16:25 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1661278586; bh=haZ+tNBZsCquW96v1sffE2Umzn+o3GD60ShlCxbHURo=; h=Date:Subject:To:Cc:References:From:In-Reply-To; b=BJWNJJAUmGPwQWPWQRy0FzHO6hb+ctaaWhYv/lell/i/mE7oGmS4vHdTKe4c9xPIq BGZE+bVJiA9gttYaZGRaS2IG8op9yxHJu49suu4i1KXi38gVUi+tkeWs3BGMrgGwv6 YsuS6m0LdrnDfP9bVsZOoFBYfR8aRhq/oTtCQPL8= Content-Type: multipart/alternative; boundary="------------KgrGGl90wz7tcBrB1RtKBcqD" Message-ID: Date: Tue, 23 Aug 2022 21:16:24 +0300 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.11.0 Subject: Re: SQL/JSON features for v15 Content-Language: en-US To: Pavel Stehule , Andres Freund Cc: Robert Haas , "Jonathan S. Katz" , PostgreSQL Hackers , Andrew Dunstan , Tom Lane , Michael Paquier , John Naylor , Amit Langote References: <20220815223853.itrlpqgpqheg6r6a@awork3.anarazel.de> <225d341b-f310-eed0-ceff-b8ec61669d9f@postgrespro.ru> <20220816021425.5sa6gx67zmpou3bc@awork3.anarazel.de> <8d0ffc54-469a-ffab-68a3-8b547e24a08d@postgresql.org> <7d83684b-7932-9f29-400b-0beedfafcdd4@postgrespro.ru> <69022273-e7e3-6d1a-10f8-d1b4740e4b97@postgresql.org> <92cab50c-152b-84fc-fd49-12b936113a3d@postgresql.org> <20220823155511.ffyntlyjl6wrgljv@awork3.anarazel.de> <20220823172718.o4p25k2bcrmsnj3a@awork3.anarazel.de> From: Nikita Glukhov In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------KgrGGl90wz7tcBrB1RtKBcqD Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 23.08.2022 20:38, Pavel Stehule wrote: > út 23. 8. 2022 v 19:27 odesílatel Andres Freund > napsal: > > Hi, > > On 2022-08-23 18:06:22 +0200, Pavel Stehule wrote: > > The errors that should be handled are related to json structure > errors. I > > don't think so we have to handle all errors and all conversions. > > > > The JSON knows only three types - and these conversions can be > written > > specially for this case - or we can write json io routines to be > able to > > signal error > > without an exception. > > I think that's not true unfortunately. You can specify return > types, and > composite types can be populated. Which essentially requires arbitrary > coercions. > > > Please, can you send an example? Maybe we try to fix a feature that is > not required by standard. - Returning arbitrary types in JSON_VALUE using I/O coercion from JSON string (more precisely, text::arbitrary_type cast): SELECT JSON_QUERY(jsonb '"1, 2"', '$' RETURNING point); json_query ------------ (1,2) (1 row) - Returning composite and array types in JSON_QUERY, which is implemented reusing the code of our json[b]_populate_record[set](): SELECT JSON_QUERY(jsonb '[1, "2", null]', '$' RETURNING int[]); json_query ------------ {1,2,NULL} (1 row) -- Nikita Glukhov Postgres Professional:http://www.postgrespro.com The Russian Postgres Company --------------KgrGGl90wz7tcBrB1RtKBcqD Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 23.08.2022 20:38, Pavel Stehule wrote:

        

        

          
út 23. 8. 2022 v 19:27
            odesílatel Andres Freund <andres@anarazel.de>
            napsal:

          

          
Hi,

            

            On 2022-08-23 18:06:22 +0200, Pavel Stehule wrote:

            > The errors that should be handled are related to json
            structure errors. I

            > don't think so we have to handle all errors and all
            conversions.

            > 

            > The JSON knows only three types - and these conversions
            can be written

            > specially for this case - or we can write json io
            routines to be able to

            > signal error

            > without an exception.

            

            I think that's not true unfortunately. You can specify
            return types, and

            composite types can be populated. Which essentially requires
            arbitrary

            coercions.

          

          


          

          Please, can you send an example? Maybe we try to fix a feature
          that is not required by standard.

      

    

    
- Returning arbitrary types in JSON_VALUE using I/O coercion 
from JSON string (more precisely, text::arbitrary_type cast):

SELECT JSON_QUERY(jsonb '"1, 2"', '$' RETURNING point);
 json_query 
------------
 (1,2)
(1 row)


- Returning composite and array types in JSON_QUERY, which is implemented 
reusing the code of our json[b]_populate_record[set]():

SELECT JSON_QUERY(jsonb '[1, "2", null]', '$' RETURNING int[]);
 json_query 
------------
 {1,2,NULL}
(1 row)

-- 
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company




  


--------------KgrGGl90wz7tcBrB1RtKBcqD--