0004-Subscripting-documentation.patch

From e95d1728302b7909efb9a60354bf176f0485087b Mon Sep 17 00:00:00 2001
From: erthalion <9erthalion6@gmail.com>
Date: Tue, 29 Aug 2017 22:16:54 +0200
Subject: [PATCH 4/4] Subscripting documentation

---
 doc/src/sgml/catalogs.sgml        |   7 ++
 doc/src/sgml/extend.sgml          |   6 ++
 doc/src/sgml/filelist.sgml        |   1 +
 doc/src/sgml/json.sgml            |  25 ++++++
 doc/src/sgml/ref/create_type.sgml |  30 ++++++-
 doc/src/sgml/xsubscripting.sgml   | 102 ++++++++++++++++++++++
 src/tutorial/Makefile             |   4 +-
 src/tutorial/subscripting.c       | 172 ++++++++++++++++++++++++++++++++++++++
 src/tutorial/subscripting.source  |  81 ++++++++++++++++++
 9 files changed, 425 insertions(+), 3 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 ef7054cf26..6b8f6319b3 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -7807,6 +7807,13 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</>:<replaceable>&lt;salt&gt;<
      </row>
 
      <row>
+      <entry><structfield>typsubsparse</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, or 0 if this type doesn't support subscripting.</entry>
+     </row>
+
+     <row>
       <entry><structfield>typdefaultbin</structfield></entry>
       <entry><type>pg_node_tree</type></entry>
       <entry></entry>
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index b96ef389a2..7e8c9188d2 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -35,6 +35,11 @@
     </listitem>
     <listitem>
      <para>
+      subscripting procedure (starting in <xref linkend="xsubscripting">)
+     </para>
+    </listitem>
+    <listitem>
+     <para>
       operator classes for indexes (starting in <xref linkend="xindex">)
      </para>
     </listitem>
@@ -283,6 +288,7 @@
   &xaggr;
   &xtypes;
   &xoper;
+  &xsubscripting;
   &xindex;
 
 
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index b914086009..db38534c0c 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 7dfdf96764..e556a81a95 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -569,4 +569,29 @@ SELECT jdoc-&gt;'guid', jdoc-&gt;'name' FROM api WHERE jdoc @&gt; '{"tags": ["qu
       compared using the default database collation.
   </para>
  </sect2>
+
+ <sect2 id="jsonb-subscripting">
+  <title><type>jsonb</> Subscripting</title>
+  <para>
+   <type>jsonb</> data type supports array-style subscripting expressions to extract or update particular element. 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
+SELECT * from table_name where jsonb_field['key'] = '"value"';
+</programlisting>
+  </para>
+ </sect2>
+
+
 </sect1>
diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml
index 7146c4a27b..c3df0f9029 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 = <replaceable class="parameter">subscripting_function</replaceable> ]
 )
 
 CREATE TYPE <replaceable class="parameter">name</replaceable>
@@ -194,7 +195,8 @@ CREATE TYPE <replaceable class="parameter">name</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">analyze_function</replaceable>,
+   <replaceable class="parameter">subscripting_function</replaceable>
    are optional.  Generally these functions have to be coded in C
    or another low-level language.
   </para>
@@ -451,6 +453,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_function</replaceable>
+   contains type-specific logic for subscripting of the data type.
+   By default, there is no such function, 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</>, and return
+   a <type>internal</> result. There are two examples of implementation for
+   subscripting function in case of array
+   (<replaceable class="parameter">array_subscripting</replaceable>)
+   and jsonb
+   (<replaceable class="parameter">jsonb_subscripting</replaceable>)
+   types in <filename>src/backend/utils/adt/arrayfuncs.c</> and
+   <filename>src/backend/utils/adt/jsonfuncs.c</> corresponding.
+  </para>
   </refsect2>
 
   <refsect2>
@@ -766,6 +784,16 @@ CREATE TYPE <replaceable class="parameter">name</replaceable>
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">subscripting_function</replaceable></term>
+    <listitem>
+     <para>
+      The name of a function that contains type-specific 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..b5e909b106
--- /dev/null
+++ b/doc/src/sgml/xsubscripting.sgml
@@ -0,0 +1,102 @@
+<!-- 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 should contain logic for verification
+  and decide which function must be used for evaluation of this expression.
+  For instance:
+</para>
+<programlisting><![CDATA[
+typedef struct Custom
+{
+    int first;
+    int second;
+}   Custom;
+
+Datum
+custom_subscripting_extract(PG_FUNCTION_ARGS)
+{
+    Custom                      *result = (Custom *) sbsdata->containerSource;
+    ExprEvalStep                *step = (ExprEvalStep *) PG_GETARG_POINTER(1);
+
+    // Some assign logic based on sbsdata
+}
+
+Datum
+custom_subscripting_assign(PG_FUNCTION_ARGS)
+{
+    Custom                  *containerSource = (Custom *) PG_GETARG_DATUM(0);
+    ExprEvalStep            *step = (ExprEvalStep *) PG_GETARG_POINTER(1);
+
+    // Some extraction logic based on sbsdata
+}
+
+PG_FUNCTION_INFO_V1(custom_subscripting);
+
+Datum
+custom_subscript_parse(PG_FUNCTION_ARGS)
+{
+    bool                isAssignment = PG_GETARG_BOOL(0);
+    SubscriptingRef    *sbsref = (SubscriptingRef *) PG_GETARG_POINTER(0);
+    Datum               assign_proc = CStringGetTextDatum("custom_subscripting_assign");
+    Datum               extract_proc = CStringGetTextDatum("custom_subscripting_extract");
+
+    // Some verifications or type coersion
+
+    if (isAssignment)
+        sbsref->refevalfunc = DirectFunctionCall1(to_regproc, assign_proc);
+    else
+        sbsref->refevalfunc = DirectFunctionCall1(to_regproc, extract_proc);
+
+    PG_RETURN_POINTER(sbsref);
+}]]>
+</programlisting>
+
+<para>
+    Then you can define a subscripting procedure and a custom data type:
+</para>
+<programlisting>
+CREATE FUNCTION custom_subscript_parse(internal)
+    RETURNS internal
+    AS '<replaceable>filename</replaceable>'
+    LANGUAGE C IMMUTABLE STRICT;
+
+CREATE TYPE custom (
+   internallength = 4,
+   input = custom_in,
+   output = custom_out,
+   subscripting = custom_subscript_parse
+);
+</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</> directory of the source distribution.
+   See the <filename>README</> 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..bf47d23be5
--- /dev/null
+++ b/src/tutorial/subscripting.c
@@ -0,0 +1,172 @@
+/*
+ * 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;
+
+PG_FUNCTION_INFO_V1(custom_in);
+PG_FUNCTION_INFO_V1(custom_subscript_parse);
+PG_FUNCTION_INFO_V1(custom_subscripting_assign);
+PG_FUNCTION_INFO_V1(custom_subscripting_extract);
+
+/*****************************************************************************
+ * 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);
+}
+
+PG_FUNCTION_INFO_V1(custom_out);
+
+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_assign(PG_FUNCTION_ARGS)
+{
+	Custom						*containerSource = (Custom *) PG_GETARG_DATUM(0);
+	ExprEvalStep				*step = (ExprEvalStep *) PG_GETARG_POINTER(1);
+
+	SubscriptingRefState		*sbstate = step->d.sbsref.state;
+	int							index;
+
+	if (sbstate->numupper != 1)
+		ereport(ERROR, (errmsg("custom does not support nested subscripting")));
+
+	index = DatumGetInt32(sbstate->upper[0]);
+
+	if (index == 1)
+		containerSource->first = DatumGetInt32(sbstate->replacevalue);
+	else
+		containerSource->second = DatumGetInt32(sbstate->replacevalue);
+
+	PG_RETURN_POINTER(containerSource);
+}
+
+
+Datum
+custom_subscripting_extract(PG_FUNCTION_ARGS)
+{
+	Custom					*containerSource = (Custom *) PG_GETARG_DATUM(0);
+	ExprEvalStep			*step = (ExprEvalStep *) PG_GETARG_POINTER(1);
+	SubscriptingRefState	*sbstate = step->d.sbsref.state;
+
+	int						index;
+
+	if (sbstate->numupper != 1)
+		ereport(ERROR, (errmsg("custom does not support nested subscripting")));
+
+	index = DatumGetInt32(sbstate->upper[0]);
+
+	if (index == 1)
+		PG_RETURN_INT32(containerSource->first);
+	else
+		PG_RETURN_INT32(containerSource->second);
+}
+
+Datum
+custom_subscript_parse(PG_FUNCTION_ARGS)
+{
+	bool				isAssignment = PG_GETARG_BOOL(0);
+	SubscriptingRef	   *sbsref = (SubscriptingRef *) PG_GETARG_POINTER(1);
+	ParseState		   *pstate = (ParseState *) PG_GETARG_POINTER(2);
+	List			   *upperIndexpr = NIL;
+	ListCell		   *l;
+	Datum				assign_proc = CStringGetTextDatum("custom_subscripting_assign");
+	Datum				extract_proc = CStringGetTextDatum("custom_subscripting_extract");
+
+	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);
+
+		Assert(subexpr != NULL);
+
+		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 int type"),
+					 parser_errposition(pstate, exprLocation(subexpr))));
+
+		upperIndexpr = lappend(upperIndexpr, subexpr);
+	}
+
+	sbsref->refupperindexpr = upperIndexpr;
+	sbsref->refelemtype = INT4OID;
+
+	if (isAssignment)
+		sbsref->refevalfunc = DirectFunctionCall1(to_regproc, assign_proc);
+	else
+		sbsref->refevalfunc = DirectFunctionCall1(to_regproc, extract_proc);
+
+	PG_RETURN_POINTER(sbsref);
+}
diff --git a/src/tutorial/subscripting.source b/src/tutorial/subscripting.source
new file mode 100644
index 0000000000..818222902c
--- /dev/null
+++ b/src/tutorial/subscripting.source
@@ -0,0 +1,81 @@
+---------------------------------------------------------------------------
+--
+-- 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_subscript_parse(internal)
+   RETURNS internal
+   AS '_OBJWD_/subscripting'
+   LANGUAGE C IMMUTABLE STRICT;
+
+CREATE FUNCTION custom_subscripting_extract(internal)
+   RETURNS internal
+   AS '_OBJWD_/subscripting'
+   LANGUAGE C IMMUTABLE STRICT;
+
+CREATE FUNCTION custom_subscripting_assign(internal)
+   RETURNS internal
+   AS '_OBJWD_/subscripting'
+   LANGUAGE C IMMUTABLE STRICT;
+
+CREATE TYPE custom (
+   internallength = 8,
+   input = custom_in,
+   output = custom_out,
+   subscripting = custom_subscript_parse
+);
+
+-- 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.13.0