Re: SQL/JSON features for v15

On 23.08.2022 20:38, Pavel Stehule wrote:
> út 23. 8. 2022 v 19:27 odesílatel Andres Freund <andres(at)anarazel(dot)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