Subject: [PATCH v33 4/5] Subscripting documentation

From: erthalion <[email-masked]>
Date: Fri, 1 Feb 2019 11:47:37 +0100

Supporting documentation for generalized subscripting. It includes the
description of a new field in pg_type, the new section for jsonb
documentation about subscripting feature on this data type, and also the
tutorial about how to write subscripting operator for a custom data type.

Reviewed-by: Tom Lane, Arthur Zakirov, Oleksandr Shulgin
---
 doc/src/sgml/catalogs.sgml        |   8 ++
 doc/src/sgml/extend.sgml          |   6 +
 doc/src/sgml/filelist.sgml        |   1 +
 doc/src/sgml/json.sgml            |  39 ++++++
 doc/src/sgml/ref/create_type.sgml |  33 ++++-
 doc/src/sgml/xsubscripting.sgml   | 111 +++++++++++++++++
 src/tutorial/Makefile             |   4 +-
 src/tutorial/subscripting.c       | 201 ++++++++++++++++++++++++++++++
 src/tutorial/subscripting.source  |  71 +++++++++++
 9 files changed, 470 insertions(+), 4 deletions(-)
 create mode 100644 doc/src/sgml/xsubscripting.sgml
 create mode 100644 src/tutorial/subscripting.c
 create mode 100644 src/tutorial/subscripting.source

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 048ff284f7..b17e9ee382 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -8971,6 +8971,14 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
       </para></entry>
      </row>
 
+     <row>
+      <entry><structfield>typsubshandler</structfield></entry>
+      <entry><type>regproc</type></entry>
+      <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
+      <entry>Custom subscripting function with type-specific logic for parsing
+      and validation, or 0 if this type doesn't support subscripting.</entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>typdefault</structfield> <type>text</type>
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 890ff97b7a..65cf5706b6 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -33,6 +33,11 @@
       operators (starting in <xref linkend="xoper"/>)
      </para>
     </listitem>
+    <listitem>
+     <para>
+      subscripting procedure (starting in <xref linkend="xsubscripting"/>)
+     </para>
+    </listitem>
     <listitem>
      <para>
       operator classes for indexes (starting in <xref linkend="xindex"/>)
@@ -481,6 +486,7 @@ RETURNS anycompatible AS ...
   &xaggr;
   &xtypes;
   &xoper;
+  &xsubscripting;
   &xindex;

diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 64b5da0070..4c3fd37fc1 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -69,6 +69,7 @@
 <!ENTITY xplang     SYSTEM "xplang.sgml">
 <!ENTITY xoper      SYSTEM "xoper.sgml">
 <!ENTITY xtypes     SYSTEM "xtypes.sgml">
+<!ENTITY xsubscripting SYSTEM "xsubscripting.sgml">
 <!ENTITY plperl     SYSTEM "plperl.sgml">
 <!ENTITY plpython   SYSTEM "plpython.sgml">
 <!ENTITY plsql      SYSTEM "plpgsql.sgml">
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index c0a6554d4d..5c538dca05 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -602,6 +602,45 @@ SELECT jdoc-&gt;'guid', jdoc-&gt;'name' FROM api WHERE jdoc @&gt; '{"tags": ["qu
   </para>
  </sect2>
 
+ <sect2 id="jsonb-subscripting">
+  <title><type>jsonb</type> Subscripting</title>
+  <para>
+   <type>jsonb</type> data type supports array-style subscripting expressions
+   to extract or update particular elements. It's possible to use multiple
+   subscripting expressions to extract nested values. In this case, a chain of
+   subscripting expressions follows the same rules as the
+   <literal>path</literal> argument in <literal>jsonb_set</literal> function,
+   e.g. in case of arrays it is a 0-based operation or that negative integers
+   that appear in <literal>path</literal> count from the end of JSON arrays.
+   The result of subscripting expressions is always jsonb data type. An
+   example of subscripting syntax:
+<programlisting>
+-- Extract value by key
+SELECT ('{"a": 1}'::jsonb)['a'];
+
+-- Extract nested value by key path
+SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];
+
+-- Extract element by index
+SELECT ('[1, "2", null]'::jsonb)[1];
+
+-- Update value by key
+UPDATE table_name SET jsonb_field['key'] = 1;
+
+-- Select records using where clause with subscripting. Since the result of
+-- subscripting is jsonb and we basically want to compare two jsonb objects, we
+-- need to put the value in double quotes to be able to convert it to jsonb.
+SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';
+</programlisting>
+
+  There is no special indexing support for such kind of expressions, but you
+  can always create a functional index that includes it
+<programlisting>
+CREATE INDEX idx ON table_name ((jsonb_field['key']));
+</programlisting>
+  </para>
+ </sect2>
+
  <sect2>
   <title>Transforms</title>
 
diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml
index 111f8e65d2..a34df4d247 100644
--- a/doc/src/sgml/ref/create_type.sgml
+++ b/doc/src/sgml/ref/create_type.sgml
@@ -54,6 +54,7 @@ CREATE TYPE <replaceable class="parameter">name</replaceable> (
     [ , ELEMENT = <replaceable class="parameter">element</replaceable> ]
     [ , DELIMITER = <replaceable class="parameter">delimiter</replaceable> ]
     [ , COLLATABLE = <replaceable class="parameter">collatable</replaceable> ]
+    [ , SUBSCRIPTING_HANDLER = <replaceable class="parameter">subscripting_handler_function</replaceable> ]
 )
 
 CREATE TYPE <replaceable class="parameter">name</replaceable>
@@ -196,8 +197,9 @@ CREATE TYPE <replaceable class="parameter">name</replaceable>
    <replaceable class="parameter">receive_function</replaceable>,
    <replaceable class="parameter">send_function</replaceable>,
    <replaceable class="parameter">type_modifier_input_function</replaceable>,
-   <replaceable class="parameter">type_modifier_output_function</replaceable> and
-   <replaceable class="parameter">analyze_function</replaceable>
+   <replaceable class="parameter">type_modifier_output_function</replaceable>,
+   <replaceable class="parameter">analyze_function</replaceable>,
+   <replaceable class="parameter">subscripting_handler_function</replaceable>
    are optional.  Generally these functions have to be coded in C
    or another low-level language.
   </para>
@@ -454,6 +456,22 @@ CREATE TYPE <replaceable class="parameter">name</replaceable>
    make use of the collation information; this does not happen
    automatically merely by marking the type collatable.
   </para>
+
+  <para>
+   The optional
+   <replaceable class="parameter">subscripting_handler_function</replaceable>
+   contains type-specific logic for subscripting of the data type.
+   By default, there is no such function provided, which means that the data
+   type doesn't support subscripting. The subscripting function must be
+   declared to take a single argument of type <type>internal</type>, and return
+   a <type>internal</type> result. There are two examples of implementation for
+   subscripting functions in case of array
+   (<replaceable class="parameter">array_subscripting_handler</replaceable>)
+   and jsonb
+   (<replaceable class="parameter">jsonb_subscripting_handler</replaceable>)
+   types in <filename>src/backend/utils/adt/arrayfuncs.c</filename> and
+   <filename>src/backend/utils/adt/jsonfuncs.c</filename>, respectively.
+  </para>
   </refsect2>
 
   <refsect2>
@@ -769,6 +787,17 @@ CREATE TYPE <replaceable class="parameter">name</replaceable>
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">subscripting_handler_function</replaceable></term>
+    <listitem>
+     <para>
+      The name of a function that returns list of type-specific callback functions to
+      support subscripting logic for the data type.
+     </para>
+    </listitem>
+   </varlistentry>
+
   </variablelist>
  </refsect1>
 
diff --git a/doc/src/sgml/xsubscripting.sgml b/doc/src/sgml/xsubscripting.sgml
new file mode 100644
index 0000000000..7224e81fa2
--- /dev/null
+++ b/doc/src/sgml/xsubscripting.sgml
@@ -0,0 +1,111 @@
+<!-- doc/src/sgml/xsubscripting.sgml -->
+
+ <sect1 id="xsubscripting">
+  <title>User-defined subscripting procedure</title>
+
+  <indexterm zone="xsubscripting">
+    <primary>custom subscripting</primary>
+  </indexterm>
+  <para>
+  When you define a new base type, you can also specify a custom procedure to
+  handle subscripting expressions. It must contain logic for verification and
+  evaluation of this expression, i.e. fetching or updating some data in this
+  data type. For instance:
+</para>
+<programlisting><![CDATA[
+typedef struct Custom
+{
+    int first;
+    int second;
+}   Custom;
+
+PG_FUNCTION_INFO_V1(custom_subscripting_handler);
+
+Datum
+custom_subscripting_handler(PG_FUNCTION_ARGS)
+{
+    SubscriptRoutines *sbsroutines = (SubscriptRoutines *)
+                                     palloc(sizeof(SubscriptRoutines));
+    sbsroutines->prepare = custom_subscript_prepare;
+    sbsroutines->validate = custom_subscript_validate;
+    sbsroutines->fetch = custom_subscript_fetch;
+    sbsroutines->assign = custom_subscript_assign;
+
+    PG_RETURN_POINTER(sbsroutines);
+}
+
+SubscriptingRef *
+custom_subscript_prepare(bool isAssignment, SubscriptingRef *sbsref)
+{
+    sbsref->refelemtype = someType;
+    sbsref->refassgntype = someType;
+
+    return sbsref;
+}
+
+SubscriptingRef *
+custom_subscript_validate(bool isAssignment, SubscriptingRef *sbsref,
+                          ParseState *pstate)
+{
+    // some validation and coercion logic
+
+    return sbsref;
+}
+
+Datum
+custom_subscript_assign(Datum containerSource, SubscriptingRefState *sbstate)
+{
+    // Some assignment logic
+
+    return newContainer;
+}
+
+Datum
+custom_subscript_fetch(Datum containerSource, SubscriptingRefState *sbstate)
+{
+    // Some fetch logic based on sbstate
+}]]>
+</programlisting>
+
+<para>
+    Then you can define a subscripting procedure and a custom data type:
+</para>
+<programlisting>
+CREATE FUNCTION custom_subscripting_handler(internal)
+    RETURNS internal
+    AS '<replaceable>filename</replaceable>'
+    LANGUAGE C IMMUTABLE STRICT;
+
+CREATE TYPE custom (
+   internallength = 4,
+   input = custom_in,
+   output = custom_out,
+   subscripting_handler = custom_subscripting_handler,
+);
+</programlisting>
+
+<para>
+    and use it as usual:
+</para>
+<programlisting>
+CREATE TABLE test_subscripting (
+    data    custom
+);
+
+INSERT INTO test_subscripting VALUES ('(1, 2)');
+
+SELECT data[0] from test_subscripting;
+
+UPDATE test_subscripting SET data[1] = 3;
+</programlisting>
+
+
+  <para>
+   The examples of custom subscripting implementation can be found in
+   <filename>subscripting.sql</filename> and <filename>subscripting.c</filename>
+   in the <filename>src/tutorial</filename> directory of the source distribution.
+   See the <filename>README</filename> file in that directory for instructions
+   about running the examples.
+  </para>
+
+</sect1>
diff --git a/src/tutorial/Makefile b/src/tutorial/Makefile
index 16dc390f71..0ead60c2d4 100644
--- a/src/tutorial/Makefile
+++ b/src/tutorial/Makefile
@@ -13,8 +13,8 @@
 #
 #-------------------------------------------------------------------------
 
-MODULES = complex funcs
-DATA_built = advanced.sql basics.sql complex.sql funcs.sql syscat.sql
+MODULES = complex funcs subscripting
+DATA_built = advanced.sql basics.sql complex.sql funcs.sql syscat.sql subscripting.sql
 
 ifdef NO_PGXS
 subdir = src/tutorial
diff --git a/src/tutorial/subscripting.c b/src/tutorial/subscripting.c
new file mode 100644
index 0000000000..1eb8c45652
--- /dev/null
+++ b/src/tutorial/subscripting.c
@@ -0,0 +1,201 @@
+/*
+ * src/tutorial/subscripting.c
+ *
+ ******************************************************************************
+  This file contains routines that can be bound to a Postgres backend and
+  called by the backend in the process of processing queries.  The calling
+  format for these routines is dictated by Postgres architecture.
+******************************************************************************/
+
+#include "postgres.h"
+
+#include "catalog/pg_type.h"
+#include "executor/executor.h"
+#include "executor/execExpr.h"
+#include "nodes/nodeFuncs.h"
+#include "parser/parse_coerce.h"
+#include "utils/builtins.h"
+#include "utils/fmgrprotos.h"
+
+PG_MODULE_MAGIC;
+
+typedef struct Custom
+{
+	int	first;
+	int	second;
+}	Custom;
+
+SubscriptingRef * custom_subscript_prepare(bool isAssignment, SubscriptingRef *sbsref);
+SubscriptingRef * custom_subscript_validate(bool isAssignment, SubscriptingRef *sbsref,
+											ParseState *pstate);
+Datum custom_subscript_fetch(Datum containerSource, SubscriptingRefState *sbstate);
+Datum custom_subscript_assign(Datum containerSource, SubscriptingRefState *sbstate);
+
+PG_FUNCTION_INFO_V1(custom_in);
+PG_FUNCTION_INFO_V1(custom_out);
+PG_FUNCTION_INFO_V1(custom_subscripting_handler);
+
+/*****************************************************************************
+ * Input/Output functions
+ *****************************************************************************/
+
+Datum
+custom_in(PG_FUNCTION_ARGS)
+{
+	char	*str = PG_GETARG_CSTRING(0);
+	int		firstValue,
+			secondValue;
+	Custom	*result;
+
+	if (sscanf(str, " ( %d , %d )", &firstValue, &secondValue) != 2)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+				 errmsg("invalid input syntax for complex: \"%s\"",
+						str)));
+
+
+	result = (Custom *) palloc(sizeof(Custom));
+	result->first = firstValue;
+	result->second = secondValue;
+	PG_RETURN_POINTER(result);
+}
+
+Datum
+custom_out(PG_FUNCTION_ARGS)
+{
+	Custom	*custom = (Custom *) PG_GETARG_POINTER(0);
+	char	*result;
+
+	result = psprintf("(%d, %d)", custom->first, custom->second);
+	PG_RETURN_CSTRING(result);
+}
+
+/*****************************************************************************
+ * Custom subscripting logic functions
+ *****************************************************************************/
+
+Datum
+custom_subscripting_handler(PG_FUNCTION_ARGS)
+{
+	SubscriptRoutines *sbsroutines = (SubscriptRoutines *)
+									 palloc(sizeof(SubscriptRoutines));
+
+	sbsroutines->prepare = custom_subscript_prepare;
+	sbsroutines->validate = custom_subscript_validate;
+	sbsroutines->fetch = custom_subscript_fetch;
+	sbsroutines->assign = custom_subscript_assign;
+
+	PG_RETURN_POINTER(sbsroutines);
+}
+
+SubscriptingRef *
+custom_subscript_prepare(bool isAssignment, SubscriptingRef *sbsref)
+{
+	sbsref->refelemtype = INT4OID;
+	sbsref->refassgntype = INT4OID;
+	return sbsref;
+}
+
+SubscriptingRef *
+custom_subscript_validate(bool isAssignment, SubscriptingRef *sbsref,
+						  ParseState *pstate)
+{
+	List			   *upperIndexpr = NIL;
+	ListCell		   *l;
+
+	if (sbsref->reflowerindexpr != NIL)
+		ereport(ERROR,
+				(errcode(ERRCODE_DATATYPE_MISMATCH),
+				 errmsg("custom subscript does not support slices"),
+				 parser_errposition(pstate, exprLocation(
+						 ((Node *)lfirst(sbsref->reflowerindexpr->head))))));
+
+	foreach(l, sbsref->refupperindexpr)
+	{
+		Node *subexpr = (Node *) lfirst(l);
+
+		if (subexpr == NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATATYPE_MISMATCH),
+					 errmsg("custom subscript does not support slices"),
+					 parser_errposition(pstate, exprLocation(
+						((Node *) lfirst(sbsref->refupperindexpr->head))))));
+
+		subexpr = coerce_to_target_type(pstate,
+										subexpr, exprType(subexpr),
+										INT4OID, -1,
+										COERCION_ASSIGNMENT,
+										COERCE_IMPLICIT_CAST,
+										-1);
+		if (subexpr == NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATATYPE_MISMATCH),
+					 errmsg("custom subscript must have integer type"),
+					 parser_errposition(pstate, exprLocation(subexpr))));
+
+		upperIndexpr = lappend(upperIndexpr, subexpr);
+
+		if (isAssignment)
+		{
+			Node *assignExpr = (Node *) sbsref->refassgnexpr;
+			Node *new_from;
+
+			new_from = coerce_to_target_type(pstate,
+					assignExpr, exprType(assignExpr),
+					INT4OID, -1,
+					COERCION_ASSIGNMENT,
+					COERCE_IMPLICIT_CAST,
+					-1);
+			if (new_from == NULL)
+				ereport(ERROR,
+						(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("custom assignment requires int type"),
+						 errhint("You will need to rewrite or cast the expression."),
+						 parser_errposition(pstate, exprLocation(assignExpr))));
+			sbsref->refassgnexpr = (Expr *)new_from;
+		}
+	}
+
+	sbsref->refupperindexpr = upperIndexpr;
+
+	return sbsref;
+}
+
+Datum
+custom_subscript_fetch(Datum containerSource, SubscriptingRefState *sbstate)
+{
+	Custom	*container= (Custom *) containerSource;
+	int		 index;
+
+	if (sbstate->numupper != 1)
+		ereport(ERROR, (errmsg("custom does not support nested subscripting")));
+
+	index = DatumGetInt32(sbstate->upperindex[0]);
+
+	if (index == 1)
+		return (Datum) container->first;
+	else
+		return (Datum) container->second;
+}
+
+Datum
+custom_subscript_assign(Datum containerSource, SubscriptingRefState *sbstate)
+{
+	int	index;
+	Custom *container = (Custom *) containerSource;
+
+	if (sbstate->resnull)
+		return containerSource;
+
+	if (sbstate->numupper != 1)
+		ereport(ERROR, (errmsg("custom does not support nested subscripting")));
+
+	index = DatumGetInt32(sbstate->upperindex[0]);
+
+	if (index == 1)
+		container->first = DatumGetInt32(sbstate->replacevalue);
+	else
+		container->second = DatumGetInt32(sbstate->replacevalue);
+
+	return (Datum) container;
+}
diff --git a/src/tutorial/subscripting.source b/src/tutorial/subscripting.source
new file mode 100644
index 0000000000..837cf30612
--- /dev/null
+++ b/src/tutorial/subscripting.source
@@ -0,0 +1,71 @@
+---------------------------------------------------------------------------
+--
+-- subscripting.sql-
+--    This file shows how to create a new subscripting procedure for
+--    user-defined type.
+--
+--
+-- Portions Copyright (c) 1996-2016, PostgreSQL Global Development Group
+-- Portions Copyright (c) 1994, Regents of the University of California
+--
+-- src/tutorial/subscripting.source
+--
+---------------------------------------------------------------------------
+
+-----------------------------
+-- Creating a new type:
+--	We are going to create a new type called 'complex' which represents
+--	complex numbers.
+--	A user-defined type must have an input and an output function, and
+--	optionally can have binary input and output functions.  All of these
+--	are usually user-defined C functions.
+-----------------------------
+
+-- Assume the user defined functions are in /home/erthalion/programms/postgresql-master/src/tutorial/complex$DLSUFFIX
+-- (we do not want to assume this is in the dynamic loader search path).
+-- Look at $PWD/complex.c for the source.  Note that we declare all of
+-- them as STRICT, so we do not need to cope with NULL inputs in the
+-- C code.  We also mark them IMMUTABLE, since they always return the
+-- same outputs given the same inputs.
+
+-- the input function 'complex_in' takes a null-terminated string (the
+-- textual representation of the type) and turns it into the internal
+-- (in memory) representation. You will get a message telling you 'complex'
+-- does not exist yet but that's okay.
+
+CREATE FUNCTION custom_in(cstring)
+   RETURNS custom
+   AS '_OBJWD_/subscripting'
+   LANGUAGE C IMMUTABLE STRICT;
+
+-- the output function 'complex_out' takes the internal representation and
+-- converts it into the textual representation.
+
+CREATE FUNCTION custom_out(custom)
+   RETURNS cstring
+   AS '_OBJWD_/subscripting'
+   LANGUAGE C IMMUTABLE STRICT;
+
+CREATE FUNCTION custom_subscripting_handler(internal)
+   RETURNS internal
+   AS '_OBJWD_/subscripting'
+   LANGUAGE C IMMUTABLE STRICT;
+
+CREATE TYPE custom (
+   internallength = 8,
+   input = custom_in,
+   output = custom_out,
+   subscripting_handler = custom_subscripting_handler
+);
+
+-- we can use it in a table
+
+CREATE TABLE test_subscripting (
+	data	custom
+);
+
+INSERT INTO test_subscripting VALUES ('(1, 2)');
+
+SELECT data[0] from test_subscripting;
+
+UPDATE test_subscripting SET data[1] = 3;
-- 
2.21.0

--u66dlpg6gaevsofr
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v33-0005-Filling-gaps-in-jsonb-arrays.patch"