Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0ec5f7e

Browse files
committed
Allow subscripting of hstore values.
This is basically a finger exercise to prove that it's possible foran extension module to add subscripting ability. Subscripted fetchfrom an hstore is not different from the existing "hstore -> text"operator. Subscripted update does seem to be a little easier touse 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, sinceit shows what's basically the minimum-complexity way to implementsubscripting when one needn't consider nested container objects.Discussion:https://postgr.es/m/3724341.1607551174@sss.pgh.pa.us
1 parent8c15a29 commit0ec5f7e

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+
CREATEFUNCTIONhstore_subscript_handler(internal)
7+
RETURNS internal
8+
AS'MODULE_PATHNAME','hstore_subscript_handler'
9+
LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
10+
11+
ALTERTYPE hstoreSET (
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+
staticvoid
42+
hstore_subscript_transform(SubscriptingRef*sbsref,
43+
List*indirection,
44+
ParseState*pstate,
45+
boolisSlice,
46+
boolisAssignment)
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+
staticvoid
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+
intidx;
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+
staticvoid
143+
hstore_subscript_assign(ExprState*state,
144+
ExprEvalStep*op,
145+
ExprContext*econtext)
146+
{
147+
SubscriptingRefState*sbsrefstate=op->d.sbsref.state;
148+
text*key;
149+
Pairsp;
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+
ints1count=HS_COUNT(hs);
193+
intoutcount=0;
194+
intvsize;
195+
char*ps1,
196+
*bufd,
197+
*pd;
198+
HEntry*es1,
199+
*ed;
200+
ints1idx;
201+
ints2idx;
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+
intdifference;
217+
218+
if (s1idx >=s1count)
219+
difference=1;
220+
elseif (s2idx >=1)
221+
difference=-1;
222+
else
223+
{
224+
ints1keylen=HSTORE_KEYLEN(es1,s1idx);
225+
ints2keylen=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+
staticvoid
264+
hstore_exec_setup(constSubscriptingRef*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+
staticconstSubscriptRoutinessbsroutines= {
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 f2from test_json_agg) q;
366366

367+
-- Test subscripting
368+
insert into test_json_agg defaultvalues;
369+
select f2['d'], f2['x'] isnullas x_isnullfrom 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_aggset f2['d']= f2['e'], f2['x']='xyzzy';
373+
select f2from test_json_agg;
374+
367375
-- Check the hstore_hash() and hstore_hash_extended() function explicitly.
368376
SELECT vas value, hstore_hash(v)::bit(32)as standard,
369377
hstore_hash_extended(v,0)::bit(32)as extended0,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp