Re: [HACKERS] [PATCH] Generic type subscripting

út 22. 12. 2020 v 11:24 odesílatel Dmitry Dolgov <9erthalion6@gmail.com>
napsal:

> > On Fri, Dec 18, 2020 at 08:59:25PM +0100, Dmitry Dolgov wrote:
> > > On Thu, Dec 17, 2020 at 03:29:35PM -0500, Tom Lane wrote:
> > > Dmitry Dolgov <9erthalion6@gmail.com> writes:
> > > > On Thu, Dec 17, 2020 at 01:49:17PM -0500, Tom Lane wrote:
> > > >> We can certainly reconsider the API for the parsing hook if there's
> > > >> really a good reason for these to be different types, but it seems
> > > >> like that would just be encouraging poor design.
> > >
> > > > To be more specific, this is the current behaviour (an example from
> the
> > > > tests) and it doesn't seem right:
> > >
> > > >     =# update test_jsonb_subscript
> > > >        set test_json['a'] = 3 where id = 1;
> > > >     UPDATE 1
> > > >     =# select jsonb_typeof(test_json->'a')
> > > >        from test_jsonb_subscript where id = 1;
> > > >      jsonb_typeof
> > > >      --------------
> > > >       string
> > >
> > >
> > > I'm rather inclined to think that the result of subscripting a
> > > jsonb (and therefore also the required source type for assignment)
> > > should be jsonb, not just text.  In that case, something like
> > >     update ... set jsoncol['a'] = 3
> > > would fail, because there's no cast from integer to jsonb.  You'd
> > > have to write one of
> > >     update ... set jsoncol['a'] = '3'
> > >     update ... set jsoncol['a'] = '"3"'
> > > to clarify how you wanted the input to be interpreted.
> > > But that seems like a good thing, just as it is for jsonb_in.
> >
> > Yep, that makes sense, will go with this idea.
>
> Here is the new version of jsonb subscripting rebased on the committed
> infrastructure patch. I hope it will not introduce any confusion with
> the previously posted patched in this thread (about alter type subscript
> and hstore) as they are independent.
>
> There are few differences from the previous version:
>
> * No limit on number of subscripts for jsonb (as there is no intrinsic
>   limitation of this kind for jsonb).
>
> * In case of assignment via subscript now it expects the replace value
>   to be of jsonb type.
>
> * Similar to the implementation for arrays, if the source jsonb is NULL,
>   it will be replaced by an empty jsonb and the new value will be
>   assigned to it. This means:
>
>         =# select * from test_jsonb_subscript where id = 3;
>          id | test_json
>         ----+-----------
>           3 | NULL
>
>         =# update test_jsonb_subscript set test_json['a'] = '1' where id =
> 3;
>         UPDATE 1
>
>         =# select * from test_jsonb_subscript where id = 3;
>          id | test_json
>         ----+-----------
>           3 | {"a": 1}
>
>   and similar:
>
>         =# select * from test_jsonb_subscript where id = 3;
>          id | test_json
>         ----+-----------
>           3 | NULL
>
>         =# update test_jsonb_subscript set test_json[1] = '1' where id = 3;
>         UPDATE 1
>
>         =# select * from test_jsonb_subscript where id = 3;
>          id | test_json
>         ----+-----------
>           3 | {"1": 1}
>
>   The latter is probably a bit strange looking, but if there are any
> concerns
>   about this part (and in general about an assignment to jsonb which is
> NULL)
>   of the implementation it could be easily changed.
>

What is the possibility to make an array instead of a record?

I expect behave like

update x set test[1] = 10; --> "[10]";
update x set test['1'] = 10; --> "{"1": 10}"

Regards

Pavel


> * There is nothing to address question about distinguishing a regular text
>   subscript and jsonpath in the patch yet. I guess the idea would be to
> save
>   the original subscript value type before coercing it into text and allow
> a
>   type specific code to convert it back. But I'll probably do it as a
> separate
>   patch when we finish with this one.
>