> 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.