Re: SQL/JSON: functions

Attached 50th version of the patches. Only the documentation was changed
since the previous version.

On 15.07.2020 14:50, Andrew Dunstan wrote:

On 7/14/20 9:47 PM, Nikita Glukhov wrote:

On 15.07.2020 00:09, Andrew Dunstan wrote:

On 7/14/20 1:00 PM, Andrew Dunstan wrote:

To keep things moving, I've rebased these patches. However, 1) the docs
patches use <replaceble class="parameter"> in many cases where they
should now just use <parameter> 

I haven't touched <replaceable class="parameter"> yet, because I'm not sure
if <replaceable> or <parameter> is correct here at all.

Here's the relevant commit message that explains the policy:

commit 47046763c3
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Mon May 4 13:48:30 2020 -0400

    Doc: standardize markup a bit more.
   
    We had a mishmash of <replaceable>, <replaceable class="parameter">,
    and <parameter> markup for operator/function arguments.  Use <parameter>
    consistently for things that are in fact names of parameters (including
    OUT parameters), reserving <replaceable> for things that aren't.  The
    latter class includes some made-up-by-the-docs type class names, like
    "numeric_type", as well as placeholders for arguments that don't have
    well-defined types.  Possibly we could do better with those categories
    as well, but for the moment I'm content not to have parameter names
    marked up in different ways in different places.

Turns out these patches also need to get the message on the new way of
writing entries in func.sgml - I'll publish some updates on that in the
next day or so so that "make doc" will succeed.

I can do it by myself, but I just need to understand what to fix and how.

Ok, I replaced some <replaceable> with <parameter> in SQL/JSON constructors.
Also I replaced all '[]' with <optional>.

Patch #5 implements functions for new JSON type that is expected to appear in
the upcoming SQL/JSON standard:
 - JSON() is for constructing JSON typed values from JSON text.   It is almost equivalent to text::json[b] cast, except that it has additional   ability to specify WITH UNIQUE KEYS constraint.
 - JSON_SCALAR() is for constructing JSON typed values from SQL scalars.   It is equivalent to to_json[b]().
 - JSON_SERIALIZE() is for serializing JSON typed values to character strings.   It is almost equivalent to json[b]::character_type cast, but it also   supports output to bytea.

Upcoming Oracle 20c will have JSON datatype and these functions [1], so we
decided also to implement them for compatibility, despite that they do not make
sense for real PG users.

Are these functions in the standard, or are they Oracle extensions? If
the latter maybe they belong in an options extension.

The new SQL type JSON and these functions are in to the new standard SQL/JSON 2.0. 
It is at the proposal stage now, but Oracle 20c has already implemented it.

The document is not publicly available, so Oleg should have sent it to you in a 
private message.

Patch #6 allows the user to use PG jsonb type as an effective implementation of
SQL JSON type.  By explicitly setting GUC sql_json = jsonb, JSON will be mapped
to jsonb, and JSON TEXT (may be named named differently) will be mapped to json.

This seems to be a hack, but we failed to propose something more simpler.

What is going to be the effect of that on things like index expressions?
This strikes me at first glance as something of a potential footgun, but
maybe I'm being overcautious.

This allows users of 'sql_json=jsonb' to create GIN indexes on SQL type JSON
(but such creation indexes are still not SQL standard conforming). Maybe I do 
not correctly understand the question or the consequences of type name 
rewriting.

The type names are rewritten on the input at the initial parsing stage and on 
the output in format_type_be(), like it is done for "timestamp with timezone" =>
timestamptz, integer => int4.  Internal representation of query expressions 
remains the same.  Affected only representation of JSON types to/from user.