Hi!
Attached patchset implements jsonpath .datetime() method.
* 0001-datetime-in-JsonbValue-1.patch
This patch allows JsonbValue struct to hold datetime values. It
appears to be convenient since jsonpath execution engine uses
JsonbValue to store intermediate calculation results. On
serialization datetime values are converted into strings.
* 0002-datetime-conversion-for-jsonpath-1.patch
This patch adds some datetime conversion infrastructure missing
according to SQL/JSON standard. It includes FF1-FF6 format patterns,
runtime identification of datetime type, strict parsing mode.
* 0003-error-suppression-for-datetime-1.path
As jsonpath supports error suppression in general, it's required for
datetime functions too. This commit implements it in the same manner
as we did for numerics before.
* 0004-implement-jsonpath-datetime-1.path
.datetime() method itself and additionally comparison of datetime
values. Here goes a trick. Out exising jsonb_path_*() functions are
immutable, while comparison of timezoned and non-timezoned type is
obviously not. This patch makes existing immutable jsonb_path_*()
functions throw error on non-immutable comparison. Additionally it
implements stable jsonb_path_*_tz() functions, which support full set
of features.
I was going to discuss this patchset among the other SQL/JSON problems
on PGCon unconference, but I didn't make it there. I found most
questionable point in this patchset to be two sets of functions:
immutable and stable. However, I don't see better solution here: we
need immutable functions for expression indexes, and also we need
function with full set of jsonpath features, which are not all
immutable.
Sometimes immutability of jsonpath expression could be determined
runtime. When .datetime() method is used with template string
argument we may know result type in advance. Thus, in some times we
may know in advance that given jsonpath is immutable. So, we could
hack contain_mutable_functions_checker() or something to make an
exclusive heuristics for jsonb_path_*() functions. But I think it's
better to go with jsonb_path_*() and jsonb_path_*_tz() variants for
now. We could come back to idea of heuristics during consideration of
standard SQL/JSON clauses.
Any thoughts?
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company