Andy Fan <zhihui.fan1213@gmail.com> writes:
>> If you use explicit cast, then the code should not be hard, in the
>> rewrite stage all information should be known.
> Can you point to me where the code is for the XML stuff?
I think Pavel means XMLTABLE, which IMO is an ugly monstrosity of
syntax --- but count on the SQL committee to do it that way :-(.
As far as the current discussion goes, I'm strongly against
introducing new functions or operators to do the same things that
we already have perfectly good syntax for. "There's more than one
way to do it" isn't necessarily a virtue, and for sure it isn't a
virtue if people have to rewrite their existing queries to make use
of your optimization.
Also, why stop at optimizing "(jsonbval->'fld')::sometype"? There are
many other extraction cases that people might wish were faster, such
as json array indexing, nested fields, etc. It certainly won't make
sense to introduce yet another set of functions for each pattern you
want to optimize --- or at least, we won't want to ask users to change
their queries to invoke those functions explicitly.
I do like the idea of attaching a Simplify planner support function
to jsonb_numeric (and any other ones that seem worth optimizing)
that can convert a stack of jsonb transformations into a bundled
operation that avoids unnecessary conversions. Then you get the
speedup without any need for people to change their existing queries.
We'd still have functions like jsonb_field_as_numeric() under the
hood, but there's not an expectation that users call them explicitly.
(Alternatively, the output of this Simplify could be a new kind of
expression node that bundles one or more jsonb extractions with a
type conversion. I don't have an opinion yet on which way is better.)
regards, tom lane