Skip to content

Commit 0ec5f7e

Browse files
tglsfdctglsfdc
committed
Allow subscripting of hstore values.
This is basically a finger exercise to prove that it's possible for an extension module to add subscripting ability. Subscripted fetch from an hstore is not different from the existing "hstore -> text" operator. Subscripted update does seem to be a little easier to use than the traditional update method using hstore concatenation, but it's not a fundamentally new ability. However, there may be some value in the code as sample code, since it shows what's basically the minimum-complexity way to implement subscripting when one needn't consider nested container objects. Discussion: https://postgr.es/m/3724341.1607551174@sss.pgh.pa.us
1 parent 8c15a29 commit 0ec5f7e

File tree

8 files changed

+392
-5
lines changed

8 files changed

+392
-5
lines changed

‎contrib/hstore/Makefile

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,10 +7,12 @@ OBJS = \
77
hstore_gin.o \
88
hstore_gist.o \
99
hstore_io.o \
10-
hstore_op.o
10+
hstore_op.o \
11+
hstore_subs.o
1112

1213
EXTENSION = hstore
1314
DATA = hstore--1.4.sql \
15+
hstore--1.7--1.8.sql \
1416
hstore--1.6--1.7.sql \
1517
hstore--1.5--1.6.sql \
1618
hstore--1.4--1.5.sql \

‎contrib/hstore/expected/hstore.out

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1560,6 +1560,29 @@ select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_jso
15601560
{"f1":"rec2","f2":{"b": false, "c": "null", "d": -12345, "e": "012345.6", "f": -1.234, "g": 0.345e-4, "a key": 2}}]
15611561
(1 row)
15621562

1563+
-- Test subscripting
1564+
insert into test_json_agg default values;
1565+
select f2['d'], f2['x'] is null as x_isnull from test_json_agg;
1566+
f2 | x_isnull
1567+
--------+----------
1568+
12345 | t
1569+
-12345 | t
1570+
| t
1571+
(3 rows)
1572+
1573+
select f2['d']['e'] from test_json_agg; -- error
1574+
ERROR: hstore allows only one subscript
1575+
select f2['d':'e'] from test_json_agg; -- error
1576+
ERROR: hstore allows only one subscript
1577+
update test_json_agg set f2['d'] = f2['e'], f2['x'] = 'xyzzy';
1578+
select f2 from test_json_agg;
1579+
f2
1580+
---------------------------------------------------------------------------------------------------------------------
1581+
"b"=>"t", "c"=>NULL, "d"=>"012345", "e"=>"012345", "f"=>"1.234", "g"=>"2.345e+4", "x"=>"xyzzy", "a key"=>"1"
1582+
"b"=>"f", "c"=>"null", "d"=>"012345.6", "e"=>"012345.6", "f"=>"-1.234", "g"=>"0.345e-4", "x"=>"xyzzy", "a key"=>"2"
1583+
"d"=>NULL, "x"=>"xyzzy"
1584+
(3 rows)
1585+
15631586
-- Check the hstore_hash() and hstore_hash_extended() function explicitly.
15641587
SELECT v as value, hstore_hash(v)::bit(32) as standard,
15651588
hstore_hash_extended(v, 0)::bit(32) as extended0,

‎contrib/hstore/hstore--1.7--1.8.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
/* contrib/hstore/hstore--1.7--1.8.sql */
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use "ALTER EXTENSION hstore UPDATE TO '1.8'" to load this file. \quit
5+
6+
CREATE FUNCTION hstore_subscript_handler(internal)
7+
RETURNS internal
8+
AS 'MODULE_PATHNAME', 'hstore_subscript_handler'
9+
LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
10+
11+
ALTER TYPE hstore SET (
12+
SUBSCRIPT = hstore_subscript_handler
13+
);

‎contrib/hstore/hstore.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
# hstore extension
22
comment = 'data type for storing sets of (key, value) pairs'
3-
default_version = '1.7'
3+
default_version = '1.8'
44
module_pathname = '$libdir/hstore'
55
relocatable = true
66
trusted = true

‎contrib/hstore/hstore_subs.c

Lines changed: 297 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,297 @@
1+
/*-------------------------------------------------------------------------
2+
*
3+
* hstore_subs.c
4+
* Subscripting support functions for hstore.
5+
*
6+
* This is a great deal simpler than array_subs.c, because the result of
7+
* subscripting an hstore is just a text string (the value for the key).
8+
* We do not need to support array slicing notation, nor multiple subscripts.
9+
* Less obviously, because the subscript result is never a SQL container
10+
* type, there will never be any nested-assignment scenarios, so we do not
11+
* need a fetch_old function. In turn, that means we can drop the
12+
* check_subscripts function and just let the fetch and assign functions
13+
* do everything.
14+
*
15+
* Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group
16+
* Portions Copyright (c) 1994, Regents of the University of California
17+
*
18+
*
19+
* IDENTIFICATION
20+
* contrib/hstore/hstore_subs.c
21+
*
22+
*-------------------------------------------------------------------------
23+
*/
24+
#include "postgres.h"
25+
26+
#include "executor/execExpr.h"
27+
#include "hstore.h"
28+
#include "nodes/nodeFuncs.h"
29+
#include "nodes/subscripting.h"
30+
#include "parser/parse_coerce.h"
31+
#include "parser/parse_expr.h"
32+
#include "utils/builtins.h"
33+
34+
35+
/*
36+
* Finish parse analysis of a SubscriptingRef expression for hstore.
37+
*
38+
* Verify there's just one subscript, coerce it to text,
39+
* and set the result type of the SubscriptingRef node.
40+
*/
41+
static void
42+
hstore_subscript_transform(SubscriptingRef *sbsref,
43+
List *indirection,
44+
ParseState *pstate,
45+
bool isSlice,
46+
bool isAssignment)
47+
{
48+
A_Indices *ai;
49+
Node *subexpr;
50+
51+
/* We support only single-subscript, non-slice cases */
52+
if (isSlice || list_length(indirection) != 1)
53+
ereport(ERROR,
54+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
55+
errmsg("hstore allows only one subscript"),
56+
parser_errposition(pstate,
57+
exprLocation((Node *) indirection))));
58+
59+
/* Transform the subscript expression to type text */
60+
ai = linitial_node(A_Indices, indirection);
61+
Assert(ai->uidx != NULL && ai->lidx == NULL && !ai->is_slice);
62+
63+
subexpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
64+
/* If it's not text already, try to coerce */
65+
subexpr = coerce_to_target_type(pstate,
66+
subexpr, exprType(subexpr),
67+
TEXTOID, -1,
68+
COERCION_ASSIGNMENT,
69+
COERCE_IMPLICIT_CAST,
70+
-1);
71+
if (subexpr == NULL)
72+
ereport(ERROR,
73+
(errcode(ERRCODE_DATATYPE_MISMATCH),
74+
errmsg("hstore subscript must have type text"),
75+
parser_errposition(pstate, exprLocation(ai->uidx))));
76+
77+
/* ... and store the transformed subscript into the SubscriptRef node */
78+
sbsref->refupperindexpr = list_make1(subexpr);
79+
sbsref->reflowerindexpr = NIL;
80+
81+
/* Determine the result type of the subscripting operation; always text */
82+
sbsref->refrestype = TEXTOID;
83+
sbsref->reftypmod = -1;
84+
}
85+
86+
/*
87+
* Evaluate SubscriptingRef fetch for hstore.
88+
*
89+
* Source container is in step's result variable (it's known not NULL, since
90+
* we set fetch_strict to true), and the subscript expression is in the
91+
* upperindex[] array.
92+
*/
93+
static void
94+
hstore_subscript_fetch(ExprState *state,
95+
ExprEvalStep *op,
96+
ExprContext *econtext)
97+
{
98+
SubscriptingRefState *sbsrefstate = op->d.sbsref.state;
99+
HStore *hs;
100+
text *key;
101+
HEntry *entries;
102+
int idx;
103+
text *out;
104+
105+
/* Should not get here if source hstore is null */
106+
Assert(!(*op->resnull));
107+
108+
/* Check for null subscript */
109+
if (sbsrefstate->upperindexnull[0])
110+
{
111+
*op->resnull = true;
112+
return;
113+
}
114+
115+
/* OK, fetch/detoast the hstore and subscript */
116+
hs = DatumGetHStoreP(*op->resvalue);
117+
key = DatumGetTextPP(sbsrefstate->upperindex[0]);
118+
119+
/* The rest is basically the same as hstore_fetchval() */
120+
entries = ARRPTR(hs);
121+
idx = hstoreFindKey(hs, NULL,
122+
VARDATA_ANY(key), VARSIZE_ANY_EXHDR(key));
123+
124+
if (idx < 0 || HSTORE_VALISNULL(entries, idx))
125+
{
126+
*op->resnull = true;
127+
return;
128+
}
129+
130+
out = cstring_to_text_with_len(HSTORE_VAL(entries, STRPTR(hs), idx),
131+
HSTORE_VALLEN(entries, idx));
132+
133+
*op->resvalue = PointerGetDatum(out);
134+
}
135+
136+
/*
137+
* Evaluate SubscriptingRef assignment for hstore.
138+
*
139+
* Input container (possibly null) is in result area, replacement value is in
140+
* SubscriptingRefState's replacevalue/replacenull.
141+
*/
142+
static void
143+
hstore_subscript_assign(ExprState *state,
144+
ExprEvalStep *op,
145+
ExprContext *econtext)
146+
{
147+
SubscriptingRefState *sbsrefstate = op->d.sbsref.state;
148+
text *key;
149+
Pairs p;
150+
HStore *out;
151+
152+
/* Check for null subscript */
153+
if (sbsrefstate->upperindexnull[0])
154+
ereport(ERROR,
155+
(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
156+
errmsg("hstore subscript in assignment must not be null")));
157+
158+
/* OK, fetch/detoast the subscript */
159+
key = DatumGetTextPP(sbsrefstate->upperindex[0]);
160+
161+
/* Create a Pairs entry for subscript + replacement value */
162+
p.needfree = false;
163+
p.key = VARDATA_ANY(key);
164+
p.keylen = hstoreCheckKeyLen(VARSIZE_ANY_EXHDR(key));
165+
166+
if (sbsrefstate->replacenull)
167+
{
168+
p.vallen = 0;
169+
p.isnull = true;
170+
}
171+
else
172+
{
173+
text *val = DatumGetTextPP(sbsrefstate->replacevalue);
174+
175+
p.val = VARDATA_ANY(val);
176+
p.vallen = hstoreCheckValLen(VARSIZE_ANY_EXHDR(val));
177+
p.isnull = false;
178+
}
179+
180+
if (*op->resnull)
181+
{
182+
/* Just build a one-element hstore (cf. hstore_from_text) */
183+
out = hstorePairs(&p, 1, p.keylen + p.vallen);
184+
}
185+
else
186+
{
187+
/*
188+
* Otherwise, merge the new key into the hstore. Based on
189+
* hstore_concat.
190+
*/
191+
HStore *hs = DatumGetHStoreP(*op->resvalue);
192+
int s1count = HS_COUNT(hs);
193+
int outcount = 0;
194+
int vsize;
195+
char *ps1,
196+
*bufd,
197+
*pd;
198+
HEntry *es1,
199+
*ed;
200+
int s1idx;
201+
int s2idx;
202+
203+
/* Allocate result without considering possibility of duplicate */
204+
vsize = CALCDATASIZE(s1count + 1, VARSIZE(hs) + p.keylen + p.vallen);
205+
out = palloc(vsize);
206+
SET_VARSIZE(out, vsize);
207+
HS_SETCOUNT(out, s1count + 1);
208+
209+
ps1 = STRPTR(hs);
210+
bufd = pd = STRPTR(out);
211+
es1 = ARRPTR(hs);
212+
ed = ARRPTR(out);
213+
214+
for (s1idx = s2idx = 0; s1idx < s1count || s2idx < 1; ++outcount)
215+
{
216+
int difference;
217+
218+
if (s1idx >= s1count)
219+
difference = 1;
220+
else if (s2idx >= 1)
221+
difference = -1;
222+
else
223+
{
224+
int s1keylen = HSTORE_KEYLEN(es1, s1idx);
225+
int s2keylen = p.keylen;
226+
227+
if (s1keylen == s2keylen)
228+
difference = memcmp(HSTORE_KEY(es1, ps1, s1idx),
229+
p.key,
230+
s1keylen);
231+
else
232+
difference = (s1keylen > s2keylen) ? 1 : -1;
233+
}
234+
235+
if (difference >= 0)
236+
{
237+
HS_ADDITEM(ed, bufd, pd, p);
238+
++s2idx;
239+
if (difference == 0)
240+
++s1idx;
241+
}
242+
else
243+
{
244+
HS_COPYITEM(ed, bufd, pd,
245+
HSTORE_KEY(es1, ps1, s1idx),
246+
HSTORE_KEYLEN(es1, s1idx),
247+
HSTORE_VALLEN(es1, s1idx),
248+
HSTORE_VALISNULL(es1, s1idx));
249+
++s1idx;
250+
}
251+
}
252+
253+
HS_FINALIZE(out, outcount, bufd, pd);
254+
}
255+
256+
*op->resvalue = PointerGetDatum(out);
257+
*op->resnull = false;
258+
}
259+
260+
/*
261+
* Set up execution state for an hstore subscript operation.
262+
*/
263+
static void
264+
hstore_exec_setup(const SubscriptingRef *sbsref,
265+
SubscriptingRefState *sbsrefstate,
266+
SubscriptExecSteps *methods)
267+
{
268+
/* Assert we are dealing with one subscript */
269+
Assert(sbsrefstate->numlower == 0);
270+
Assert(sbsrefstate->numupper == 1);
271+
/* We can't check upperprovided[0] here, but it must be true */
272+
273+
/* Pass back pointers to appropriate step execution functions */
274+
methods->sbs_check_subscripts = NULL;
275+
methods->sbs_fetch = hstore_subscript_fetch;
276+
methods->sbs_assign = hstore_subscript_assign;
277+
methods->sbs_fetch_old = NULL;
278+
}
279+
280+
/*
281+
* hstore_subscript_handler
282+
* Subscripting handler for hstore.
283+
*/
284+
PG_FUNCTION_INFO_V1(hstore_subscript_handler);
285+
Datum
286+
hstore_subscript_handler(PG_FUNCTION_ARGS)
287+
{
288+
static const SubscriptRoutines sbsroutines = {
289+
.transform = hstore_subscript_transform,
290+
.exec_setup = hstore_exec_setup,
291+
.fetch_strict = true, /* fetch returns NULL for NULL inputs */
292+
.fetch_leakproof = true, /* fetch returns NULL for bad subscript */
293+
.store_leakproof = false /* ... but assignment throws error */
294+
};
295+
296+
PG_RETURN_POINTER(&sbsroutines);
297+
}

‎contrib/hstore/sql/hstore.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -364,6 +364,14 @@ insert into test_json_agg values ('rec1','"a key" =>1, b => t, c => null, d=> 12
364364
select json_agg(q) from test_json_agg q;
365365
select json_agg(q) from (select f1, hstore_to_json_loose(f2) as f2 from test_json_agg) q;
366366

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

0 commit comments

Comments
 (0)