st 30. 12. 2020 v 14:46 odesílatel Dmitry Dolgov <[email protected]>
napsal:
> > On Wed, Dec 30, 2020 at 02:45:12PM +0100, Dmitry Dolgov wrote:
> > > On Sat, Dec 26, 2020 at 01:24:04PM -0500, Tom Lane wrote:
> > >
> > > In a case like jsonpath['...'], the initially UNKNOWN-type literal
> could
> > > in theory be coerced to any of these types, so you'd have to resolve
> that
> > > case manually. The overloaded-function code has an internal preference
> > > that makes it choose TEXT if it has a choice of TEXT or some other
> target
> > > type for an UNKNOWN input (cf parse_func.c starting about line 1150),
> but
> > > if you ask can_coerce_type() it's going to say TRUE for all three
> cases.
> > >
> > > Roughly speaking, then, I think what you want to do is
> > >
> > > 1. If input type is UNKNOWNOID, choose result type TEXT.
> > >
> > > 2. Otherwise, apply can_coerce_type() to see if the input type can be
> > > coerced to int4, text, or jsonpath. If it succeeds for none or more
> > > than one of these, throw error. Otherwise choose the single successful
> > > type.
> > >
> > > 3. Apply coerce_type() to coerce to the chosen result type.
> > >
> > > 4. At runtime, examine exprType() of the input to figure out what to
> do.
> >
> > Thanks, that was super useful. Following this suggestion I've made
> > necessary adjustments for the patch. There is no jsonpath support, but
> > this could be easily added on top.
>
> And the forgotten patch itself.
>
make check fails
But I dislike two issues
1. quietly ignored update
postgres=# update foo set a['a'][10] = '20';
UPDATE 1
postgres=# select * from foo;
┌────┐
│ a │
╞════╡
│ {} │
└────┘
(1 row)
The value should be modified or there should be an error (but I prefer
implicit creating nested empty objects when it is necessary).
update foo set a['a'] = '[]';
2. The index position was ignored.
postgres=# update foo set a['a'][10] = '20';
UPDATE 1
postgres=# select * from foo;
┌─────────────┐
│ a │
╞═════════════╡
│ {"a": [20]} │
└─────────────┘
(1 row)
Notes:
1. It is very nice so casts are supported. I wrote int2jsonb cast and it
was working. Maybe we can create buildin casts for int, bigint, numeric,
boolean, date, timestamp to jsonb.
Regards
Pavel
