Re: [HACKERS] [PATCH] Generic type subscripting

Here's a couple of little finger exercises to move this along a bit.

0001 adds the ability to attach a subscript handler to an existing
data type with ALTER TYPE.  This is clearly going to be necessary
if we want extension types to be able to use this facility.  The
only thing that I think might be controversial here is that I did
not add the ability to set pg_type.typelem.  While that'd be easy
enough so far as ALTER TYPE is concerned, I'm not sure that we want
to encourage people to change it.  The dependency rules mean that
the semantics of typelem aren't something you really want to change
after-the-fact on an existing type.  Also, if we did allow it, any
existing SubscriptingRef.refelemtype values in stored views would
fail to be updated.

0002 makes use of that to support subscripting of hstore.  I'm not
sure how much we care about that from a functionality standpoint,
but it seems like it might be good to have a contrib module testing
that extensions can use this.  Also, I thought possibly an example
showing what's basically the minimum possible amount of complexity
would be good to have.  If people like this, I'll finish it up (it
lacks docs) and add it.

            regards, tom lane

diff --git a/doc/src/sgml/ref/alter_type.sgml b/doc/src/sgml/ref/alter_type.sgml
index 64bf266373..21887e88a0 100644
--- a/doc/src/sgml/ref/alter_type.sgml
+++ b/doc/src/sgml/ref/alter_type.sgml
@@ -194,6 +194,14 @@ ALTER TYPE <replaceable class="parameter">name</replaceable> SET ( <replaceable
          requires superuser privilege.
         </para>
        </listitem>
+       <listitem>
+        <para>
+         <literal>SUBSCRIPT</literal> can be set to the name of a type-specific
+         subscripting handler function, or <literal>NONE</literal> to remove
+         the type's subscripting handler function.  Using this option
+         requires superuser privilege.
+        </para>
+       </listitem>
        <listitem>
         <para>
          <literal>STORAGE</literal><indexterm>
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 29fe52d2ce..7c0b2c3bf0 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -94,6 +94,7 @@ typedef struct
     bool        updateTypmodin;
     bool        updateTypmodout;
     bool        updateAnalyze;
+    bool        updateSubscript;
     /* New values for relevant attributes */
     char        storage;
     Oid            receiveOid;
@@ -101,6 +102,7 @@ typedef struct
     Oid            typmodinOid;
     Oid            typmodoutOid;
     Oid            analyzeOid;
+    Oid            subscriptOid;
 } AlterTypeRecurseParams;

 /* Potentially set by pg_upgrade_support functions */
@@ -3885,6 +3887,18 @@ AlterType(AlterTypeStmt *stmt)
             /* Replacing an analyze function requires superuser. */
             requireSuper = true;
         }
+        else if (strcmp(defel->defname, "subscript") == 0)
+        {
+            if (defel->arg != NULL)
+                atparams.subscriptOid =
+                    findTypeSubscriptingFunction(defGetQualifiedName(defel),
+                                                 typeOid);
+            else
+                atparams.subscriptOid = InvalidOid; /* NONE, remove function */
+            atparams.updateSubscript = true;
+            /* Replacing a subscript function requires superuser. */
+            requireSuper = true;
+        }

         /*
          * The rest of the options that CREATE accepts cannot be changed.
@@ -4042,6 +4056,11 @@ AlterTypeRecurse(Oid typeOid, bool isImplicitArray,
         replaces[Anum_pg_type_typanalyze - 1] = true;
         values[Anum_pg_type_typanalyze - 1] = ObjectIdGetDatum(atparams->analyzeOid);
     }
+    if (atparams->updateSubscript)
+    {
+        replaces[Anum_pg_type_typsubscript - 1] = true;
+        values[Anum_pg_type_typsubscript - 1] = ObjectIdGetDatum(atparams->subscriptOid);
+    }

     newtup = heap_modify_tuple(tup, RelationGetDescr(catalog),
                                values, nulls, replaces);
@@ -4098,6 +4117,7 @@ AlterTypeRecurse(Oid typeOid, bool isImplicitArray,
     atparams->updateReceive = false;    /* domains use F_DOMAIN_RECV */
     atparams->updateTypmodin = false;    /* domains don't have typmods */
     atparams->updateTypmodout = false;
+    atparams->updateSubscript = false;    /* domains don't have subscriptors */

     /* Skip the scan if nothing remains to be done */
     if (!(atparams->updateStorage ||
diff --git a/src/test/regress/expected/create_type.out b/src/test/regress/expected/create_type.out
index f85afcb31e..14394cc95c 100644
--- a/src/test/regress/expected/create_type.out
+++ b/src/test/regress/expected/create_type.out
@@ -260,38 +260,40 @@ ALTER TYPE myvarchar SET (
     receive = myvarcharrecv,
     typmod_in = varchartypmodin,
     typmod_out = varchartypmodout,
-    analyze = array_typanalyze  -- bogus, but it doesn't matter
+    -- these are bogus, but it's safe as long as we don't use the type:
+    analyze = ts_typanalyze,
+    subscript = raw_array_subscript_handler
 );
 SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
-       typanalyze, typstorage
+       typanalyze, typsubscript, typstorage
 FROM pg_type WHERE typname = 'myvarchar';
-  typinput   |  typoutput   |  typreceive   |    typsend    |    typmodin     |    typmodout     |    typanalyze    |
typstorage 

--------------+--------------+---------------+---------------+-----------------+------------------+------------------+------------
- myvarcharin | myvarcharout | myvarcharrecv | myvarcharsend | varchartypmodin | varchartypmodout | array_typanalyze |
x
+  typinput   |  typoutput   |  typreceive   |    typsend    |    typmodin     |    typmodout     |  typanalyze   |
  typsubscript         | typstorage  

+-------------+--------------+---------------+---------------+-----------------+------------------+---------------+-----------------------------+------------
+ myvarcharin | myvarcharout | myvarcharrecv | myvarcharsend | varchartypmodin | varchartypmodout | ts_typanalyze |
raw_array_subscript_handler| x 
 (1 row)

 SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
-       typanalyze, typstorage
+       typanalyze, typsubscript, typstorage
 FROM pg_type WHERE typname = '_myvarchar';
- typinput | typoutput | typreceive |  typsend   |    typmodin     |    typmodout     |    typanalyze    | typstorage
-----------+-----------+------------+------------+-----------------+------------------+------------------+------------
- array_in | array_out | array_recv | array_send | varchartypmodin | varchartypmodout | array_typanalyze | x
+ typinput | typoutput | typreceive |  typsend   |    typmodin     |    typmodout     |    typanalyze    |
typsubscript      | typstorage  

+----------+-----------+------------+------------+-----------------+------------------+------------------+-------------------------+------------
+ array_in | array_out | array_recv | array_send | varchartypmodin | varchartypmodout | array_typanalyze |
array_subscript_handler| x 
 (1 row)

 SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
-       typanalyze, typstorage
+       typanalyze, typsubscript, typstorage
 FROM pg_type WHERE typname = 'myvarchardom';
- typinput  |  typoutput   | typreceive  |    typsend    | typmodin | typmodout |    typanalyze    | typstorage
------------+--------------+-------------+---------------+----------+-----------+------------------+------------
- domain_in | myvarcharout | domain_recv | myvarcharsend | -        | -         | array_typanalyze | x
+ typinput  |  typoutput   | typreceive  |    typsend    | typmodin | typmodout |  typanalyze   | typsubscript |
typstorage 

+-----------+--------------+-------------+---------------+----------+-----------+---------------+--------------+------------
+ domain_in | myvarcharout | domain_recv | myvarcharsend | -        | -         | ts_typanalyze | -            | x
 (1 row)

 SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
-       typanalyze, typstorage
+       typanalyze, typsubscript, typstorage
 FROM pg_type WHERE typname = '_myvarchardom';
- typinput | typoutput | typreceive |  typsend   | typmodin | typmodout |    typanalyze    | typstorage
-----------+-----------+------------+------------+----------+-----------+------------------+------------
- array_in | array_out | array_recv | array_send | -        | -         | array_typanalyze | x
+ typinput | typoutput | typreceive |  typsend   | typmodin | typmodout |    typanalyze    |      typsubscript       |
typstorage 

+----------+-----------+------------+------------+----------+-----------+------------------+-------------------------+------------
+ array_in | array_out | array_recv | array_send | -        | -         | array_typanalyze | array_subscript_handler |
x
 (1 row)

 -- ensure dependencies are straight
diff --git a/src/test/regress/sql/create_type.sql b/src/test/regress/sql/create_type.sql
index 584ece0670..a32a9e6795 100644
--- a/src/test/regress/sql/create_type.sql
+++ b/src/test/regress/sql/create_type.sql
@@ -207,23 +207,25 @@ ALTER TYPE myvarchar SET (
     receive = myvarcharrecv,
     typmod_in = varchartypmodin,
     typmod_out = varchartypmodout,
-    analyze = array_typanalyze  -- bogus, but it doesn't matter
+    -- these are bogus, but it's safe as long as we don't use the type:
+    analyze = ts_typanalyze,
+    subscript = raw_array_subscript_handler
 );

 SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
-       typanalyze, typstorage
+       typanalyze, typsubscript, typstorage
 FROM pg_type WHERE typname = 'myvarchar';

 SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
-       typanalyze, typstorage
+       typanalyze, typsubscript, typstorage
 FROM pg_type WHERE typname = '_myvarchar';

 SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
-       typanalyze, typstorage
+       typanalyze, typsubscript, typstorage
 FROM pg_type WHERE typname = 'myvarchardom';

 SELECT typinput, typoutput, typreceive, typsend, typmodin, typmodout,
-       typanalyze, typstorage
+       typanalyze, typsubscript, typstorage
 FROM pg_type WHERE typname = '_myvarchardom';

 -- ensure dependencies are straight
diff --git a/contrib/hstore/Makefile b/contrib/hstore/Makefile
index 72376d9007..c4e339b57c 100644
--- a/contrib/hstore/Makefile
+++ b/contrib/hstore/Makefile
@@ -7,10 +7,12 @@ OBJS = \
     hstore_gin.o \
     hstore_gist.o \
     hstore_io.o \
-    hstore_op.o
+    hstore_op.o \
+    hstore_subs.o

 EXTENSION = hstore
 DATA = hstore--1.4.sql \
+    hstore--1.7--1.8.sql \
     hstore--1.6--1.7.sql \
     hstore--1.5--1.6.sql \
     hstore--1.4--1.5.sql \
diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out
index 8901079438..fdcc3920ce 100644
--- a/contrib/hstore/expected/hstore.out
+++ b/contrib/hstore/expected/hstore.out
@@ -1560,6 +1560,29 @@ select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_jso
   {"f1":"rec2","f2":{"b": false, "c": "null", "d": -12345, "e": "012345.6", "f": -1.234, "g": 0.345e-4, "a key": 2}}]
 (1 row)

+-- Test subscripting
+insert into test_json_agg default values;
+select f2['d'], f2['x'] is null as x_isnull from test_json_agg;
+   f2   | x_isnull
+--------+----------
+ 12345  | t
+ -12345 | t
+        | t
+(3 rows)
+
+select f2['d']['e'] from test_json_agg;  -- error
+ERROR:  hstore allows only one subscript
+select f2['d':'e'] from test_json_agg;  -- error
+ERROR:  hstore allows only one subscript
+update test_json_agg set f2['d'] = f2['e'], f2['x'] = 'xyzzy';
+select f2 from test_json_agg;
+                                                         f2
+---------------------------------------------------------------------------------------------------------------------
+ "b"=>"t", "c"=>NULL, "d"=>"012345", "e"=>"012345", "f"=>"1.234", "g"=>"2.345e+4", "x"=>"xyzzy", "a key"=>"1"
+ "b"=>"f", "c"=>"null", "d"=>"012345.6", "e"=>"012345.6", "f"=>"-1.234", "g"=>"0.345e-4", "x"=>"xyzzy", "a key"=>"2"
+ "d"=>NULL, "x"=>"xyzzy"
+(3 rows)
+
 -- Check the hstore_hash() and hstore_hash_extended() function explicitly.
 SELECT v as value, hstore_hash(v)::bit(32) as standard,
        hstore_hash_extended(v, 0)::bit(32) as extended0,
diff --git a/contrib/hstore/hstore--1.7--1.8.sql b/contrib/hstore/hstore--1.7--1.8.sql
new file mode 100644
index 0000000000..d80a138465
--- /dev/null
+++ b/contrib/hstore/hstore--1.7--1.8.sql
@@ -0,0 +1,13 @@
+/* contrib/hstore/hstore--1.7--1.8.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION hstore UPDATE TO '1.8'" to load this file. \quit
+
+CREATE FUNCTION hstore_subscript_handler(internal)
+RETURNS internal
+AS 'MODULE_PATHNAME', 'hstore_subscript_handler'
+LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
+
+ALTER TYPE hstore SET (
+  SUBSCRIPT = hstore_subscript_handler
+);
diff --git a/contrib/hstore/hstore.control b/contrib/hstore/hstore.control
index f0da772429..89e3c746c4 100644
--- a/contrib/hstore/hstore.control
+++ b/contrib/hstore/hstore.control
@@ -1,6 +1,6 @@
 # hstore extension
 comment = 'data type for storing sets of (key, value) pairs'
-default_version = '1.7'
+default_version = '1.8'
 module_pathname = '$libdir/hstore'
 relocatable = true
 trusted = true
diff --git a/contrib/hstore/hstore_subs.c b/contrib/hstore/hstore_subs.c
new file mode 100644
index 0000000000..e52de04f1a
--- /dev/null
+++ b/contrib/hstore/hstore_subs.c
@@ -0,0 +1,297 @@
+/*-------------------------------------------------------------------------
+ *
+ * hstore_subs.c
+ *      Subscripting support functions for hstore.
+ *
+ * This is a great deal simpler than array_subs.c, because the result of
+ * subscripting an hstore is just a text string (the value for the key).
+ * We do not need to support array slicing notation, nor multiple subscripts.
+ * Less obviously, because the subscript result is never a SQL container
+ * type, there will never be any nested-assignment scenarios, so we do not
+ * need a fetch_old function.  In turn, that means we can drop the
+ * check_subscripts function and just let the fetch and assign functions
+ * do everything.
+ *
+ * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ *      contrib/hstore/hstore_subs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "executor/execExpr.h"
+#include "hstore.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/subscripting.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_expr.h"
+#include "utils/builtins.h"
+
+
+/*
+ * Finish parse analysis of a SubscriptingRef expression for hstore.
+ *
+ * Verify there's just one subscript, coerce it to text,
+ * and set the result type of the SubscriptingRef node.
+ */
+static void
+hstore_subscript_transform(SubscriptingRef *sbsref,
+                           List *indirection,
+                           ParseState *pstate,
+                           bool isSlice,
+                           bool isAssignment)
+{
+    A_Indices  *ai;
+    Node       *subexpr;
+
+    /* We support only single-subscript, non-slice cases */
+    if (isSlice || list_length(indirection) != 1)
+        ereport(ERROR,
+                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                 errmsg("hstore allows only one subscript"),
+                 parser_errposition(pstate,
+                                    exprLocation((Node *) indirection))));
+
+    /* Transform the subscript expression to type text */
+    ai = linitial_node(A_Indices, indirection);
+    Assert(ai->uidx != NULL && ai->lidx == NULL && !ai->is_slice);
+
+    subexpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
+    /* If it's not text already, try to coerce */
+    subexpr = coerce_to_target_type(pstate,
+                                    subexpr, exprType(subexpr),
+                                    TEXTOID, -1,
+                                    COERCION_ASSIGNMENT,
+                                    COERCE_IMPLICIT_CAST,
+                                    -1);
+    if (subexpr == NULL)
+        ereport(ERROR,
+                (errcode(ERRCODE_DATATYPE_MISMATCH),
+                 errmsg("hstore subscript must have type text"),
+                 parser_errposition(pstate, exprLocation(ai->uidx))));
+
+    /* ... and store the transformed subscript into the SubscriptRef node */
+    sbsref->refupperindexpr = list_make1(subexpr);
+    sbsref->reflowerindexpr = NIL;
+
+    /* Determine the result type of the subscripting operation; always text */
+    sbsref->refrestype = TEXTOID;
+    sbsref->reftypmod = -1;
+}
+
+/*
+ * Evaluate SubscriptingRef fetch for hstore.
+ *
+ * Source container is in step's result variable (it's known not NULL, since
+ * we set fetch_strict to true), and the subscript expression is in the
+ * upperindex[] array.
+ */
+static void
+hstore_subscript_fetch(ExprState *state,
+                       ExprEvalStep *op,
+                       ExprContext *econtext)
+{
+    SubscriptingRefState *sbsrefstate = op->d.sbsref.state;
+    HStore       *hs;
+    text       *key;
+    HEntry       *entries;
+    int            idx;
+    text       *out;
+
+    /* Should not get here if source hstore is null */
+    Assert(!(*op->resnull));
+
+    /* Check for null subscript */
+    if (sbsrefstate->upperindexnull[0])
+    {
+        *op->resnull = true;
+        return;
+    }
+
+    /* OK, fetch/detoast the hstore and subscript */
+    hs = DatumGetHStoreP(*op->resvalue);
+    key = DatumGetTextPP(sbsrefstate->upperindex[0]);
+
+    /* The rest is basically the same as hstore_fetchval() */
+    entries = ARRPTR(hs);
+    idx = hstoreFindKey(hs, NULL,
+                        VARDATA_ANY(key), VARSIZE_ANY_EXHDR(key));
+
+    if (idx < 0 || HSTORE_VALISNULL(entries, idx))
+    {
+        *op->resnull = true;
+        return;
+    }
+
+    out = cstring_to_text_with_len(HSTORE_VAL(entries, STRPTR(hs), idx),
+                                   HSTORE_VALLEN(entries, idx));
+
+    *op->resvalue = PointerGetDatum(out);
+}
+
+/*
+ * Evaluate SubscriptingRef assignment for hstore.
+ *
+ * Input container (possibly null) is in result area, replacement value is in
+ * SubscriptingRefState's replacevalue/replacenull.
+ */
+static void
+hstore_subscript_assign(ExprState *state,
+                        ExprEvalStep *op,
+                        ExprContext *econtext)
+{
+    SubscriptingRefState *sbsrefstate = op->d.sbsref.state;
+    text       *key;
+    Pairs        p;
+    HStore       *out;
+
+    /* Check for null subscript */
+    if (sbsrefstate->upperindexnull[0])
+        ereport(ERROR,
+                (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+                 errmsg("hstore subscript in assignment must not be null")));
+
+    /* OK, fetch/detoast the subscript */
+    key = DatumGetTextPP(sbsrefstate->upperindex[0]);
+
+    /* Create a Pairs entry for subscript + replacement value */
+    p.needfree = false;
+    p.key = VARDATA_ANY(key);
+    p.keylen = hstoreCheckKeyLen(VARSIZE_ANY_EXHDR(key));
+
+    if (sbsrefstate->replacenull)
+    {
+        p.vallen = 0;
+        p.isnull = true;
+    }
+    else
+    {
+        text       *val = DatumGetTextPP(sbsrefstate->replacevalue);
+
+        p.val = VARDATA_ANY(val);
+        p.vallen = hstoreCheckValLen(VARSIZE_ANY_EXHDR(val));
+        p.isnull = false;
+    }
+
+    if (*op->resnull)
+    {
+        /* Just build a one-element hstore (cf. hstore_from_text) */
+        out = hstorePairs(&p, 1, p.keylen + p.vallen);
+    }
+    else
+    {
+        /*
+         * Otherwise, merge the new key into the hstore.  Based on
+         * hstore_concat.
+         */
+        HStore       *hs = DatumGetHStoreP(*op->resvalue);
+        int            s1count = HS_COUNT(hs);
+        int            outcount = 0;
+        int            vsize;
+        char       *ps1,
+                   *bufd,
+                   *pd;
+        HEntry       *es1,
+                   *ed;
+        int            s1idx;
+        int            s2idx;
+
+        /* Allocate result without considering possibility of duplicate */
+        vsize = CALCDATASIZE(s1count + 1, VARSIZE(hs) + p.keylen + p.vallen);
+        out = palloc(vsize);
+        SET_VARSIZE(out, vsize);
+        HS_SETCOUNT(out, s1count + 1);
+
+        ps1 = STRPTR(hs);
+        bufd = pd = STRPTR(out);
+        es1 = ARRPTR(hs);
+        ed = ARRPTR(out);
+
+        for (s1idx = s2idx = 0; s1idx < s1count || s2idx < 1; ++outcount)
+        {
+            int            difference;
+
+            if (s1idx >= s1count)
+                difference = 1;
+            else if (s2idx >= 1)
+                difference = -1;
+            else
+            {
+                int            s1keylen = HSTORE_KEYLEN(es1, s1idx);
+                int            s2keylen = p.keylen;
+
+                if (s1keylen == s2keylen)
+                    difference = memcmp(HSTORE_KEY(es1, ps1, s1idx),
+                                        p.key,
+                                        s1keylen);
+                else
+                    difference = (s1keylen > s2keylen) ? 1 : -1;
+            }
+
+            if (difference >= 0)
+            {
+                HS_ADDITEM(ed, bufd, pd, p);
+                ++s2idx;
+                if (difference == 0)
+                    ++s1idx;
+            }
+            else
+            {
+                HS_COPYITEM(ed, bufd, pd,
+                            HSTORE_KEY(es1, ps1, s1idx),
+                            HSTORE_KEYLEN(es1, s1idx),
+                            HSTORE_VALLEN(es1, s1idx),
+                            HSTORE_VALISNULL(es1, s1idx));
+                ++s1idx;
+            }
+        }
+
+        HS_FINALIZE(out, outcount, bufd, pd);
+    }
+
+    *op->resvalue = PointerGetDatum(out);
+    *op->resnull = false;
+}
+
+/*
+ * Set up execution state for an hstore subscript operation.
+ */
+static void
+hstore_exec_setup(const SubscriptingRef *sbsref,
+                  SubscriptingRefState *sbsrefstate,
+                  SubscriptExecSteps *methods)
+{
+    /* Assert we are dealing with one subscript */
+    Assert(sbsrefstate->numlower == 0);
+    Assert(sbsrefstate->numupper == 1);
+    /* We can't check upperprovided[0] here, but it must be true */
+
+    /* Pass back pointers to appropriate step execution functions */
+    methods->sbs_check_subscripts = NULL;
+    methods->sbs_fetch = hstore_subscript_fetch;
+    methods->sbs_assign = hstore_subscript_assign;
+    methods->sbs_fetch_old = NULL;
+}
+
+/*
+ * hstore_subscript_handler
+ *        Subscripting handler for hstore.
+ */
+PG_FUNCTION_INFO_V1(hstore_subscript_handler);
+Datum
+hstore_subscript_handler(PG_FUNCTION_ARGS)
+{
+    static const SubscriptRoutines sbsroutines = {
+        .transform = hstore_subscript_transform,
+        .exec_setup = hstore_exec_setup,
+        .fetch_strict = true,    /* fetch returns NULL for NULL inputs */
+        .fetch_leakproof = true,    /* fetch returns NULL for bad subscript */
+        .store_leakproof = false    /* ... but assignment throws error */
+    };
+
+    PG_RETURN_POINTER(&sbsroutines);
+}
diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql
index a6c2f3a0ce..8d96e30403 100644
--- a/contrib/hstore/sql/hstore.sql
+++ b/contrib/hstore/sql/hstore.sql
@@ -364,6 +364,14 @@ insert into test_json_agg values ('rec1','"a key" =>1, b => t, c => null, d=> 12
 select json_agg(q) from test_json_agg q;
 select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_json_agg) q;

+-- Test subscripting
+insert into test_json_agg default values;
+select f2['d'], f2['x'] is null as x_isnull from test_json_agg;
+select f2['d']['e'] from test_json_agg;  -- error
+select f2['d':'e'] from test_json_agg;  -- error
+update test_json_agg set f2['d'] = f2['e'], f2['x'] = 'xyzzy';
+select f2 from test_json_agg;
+
 -- Check the hstore_hash() and hstore_hash_extended() function explicitly.
 SELECT v as value, hstore_hash(v)::bit(32) as standard,
        hstore_hash_extended(v, 0)::bit(32) as extended0,