st 30. 12. 2020 v 14:46 odesílatel Dmitry Dolgov <9erthali...@gmail.com> 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