On 10.09.24 22:16, Tom Lane wrote:
> Peter Eisentraut <peter@eisentraut.org> writes:
>> These JSON path functions are specified by the SQL standard, so they
>> shouldn't depend on PostgreSQL-specific settings. At least in new
>> functionality we should avoid that, no?
>
> Hmm ... but does the standard precisely define the output format?
>
> Since these conversions are built on our own timestamp I/O code,
> I rather imagine there is quite a lot of behavior there that's
> not to be found in the standard. That doesn't really trouble
> me as long as the spec's behavior is a subset of it (i.e.,
> reachable as long as you've got the right parameter settings).
Actually, the standard prohibits this call:
"""
XV) If JM specifies string, then:
1) Forallj,1(one)≤j≤n,
Case:
a) If Ij is not a character string, number, or Boolean value,
then let ST be data exception — non-string SQL/JSON item (2202X).
b) Otherwise, let X be an SQL variable whose value is Ij. Let ML be an
implementation-defined (IL006) maximum
length of variable-length character strings. Let Vj be the result of
CAST (X AS CHARACTER VARYING(ML)
If this conversion results in an exception condition, then
let ST be that exception condition.
"""
So I guess we have extended this and the current behavior is consistent
with item b).
What I'm concerned about is that this makes the behavior of JSON_QUERY
non-immutable. Maybe there are other reasons for it to be
non-immutable, in which case this isn't important. But it might be
worth avoiding that?
