Re: SQL/JSON: functions

On 3/2/22 10:19, Andrew Dunstan wrote:
> On 3/1/22 16:41, Andrew Dunstan wrote:
>> On 2/1/22 14:11,I wrote:
>>> 2. The new GUC "sql_json" is a bit of a worry. I understand what it's
>>> trying to do, but I'm trying to convince myself it's not going to be a
>>> fruitful source of error reports, especially if people switch it in the
>>> middle of a session. Maybe it should be an initdb option instead of a GUC?
>>>
>>>
>> So far my efforts have not borne fruit. Here's why:
>>
>>
>> andrew=# set sql_json = jsonb;
>> SET
>> andrew=# create table abc (x text, y json);
>> CREATE TABLE
>> andrew=# \d abc
>>                Table "public.abc"
>>  Column | Type | Collation | Nullable | Default
>> --------+------+-----------+----------+---------
>>  x      | text |           |          |
>>  y      | json |           |          |
>>
>> andrew=# insert into abc values ('a','{"q":1}');
>> INSERT 0 1
>> andrew=# select json_each(y) from abc;
>> ERROR:  function json_each(json) does not exist
>> LINE 1: select json_each(y) from abc;
>>                ^
>> HINT:  No function matches the given name and argument types. You might
>> need to add explicit type casts.
>> andrew=# select jsonb_each(y) from abc;
>>  jsonb_each
>> ------------
>>  (q,1)
>> (1 row)
>>
>>
>> The description tells them the column is json, but the json_* functions
>> don't work on the column and you need to use the jsonb functions. That
>> seems to me a recipe for major confusion. It might be better if we set
>> it at initdb time so it couldn't be changed, but even so it could be
>> horribly confusing.
>>
>> This is certainly severable from the rest of these patches. I'm not sure
>> how severable it is from the SQL/JSON Table patches.
>>
>>
> I have confirmed that this is not required at all for the JSON_TABLE
> patch set.
>
>
> I'll submit new patch sets omitting it shortly. The GUC patch can be
> considered separately, probably as release 16 material, but I think as
> is it's at best quite incomplete.

here's a new set of patches, omitting the GUC patch and with the
beginnings of some message cleanup - there's more work to do there.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com