Re: SQL/JSON features for v15

ú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