2 SELECT 'ff => {a=>12, b=>16}'::hstore;
4 SELECT 'ff => {a=>12, b=>16}, qq=> 123'::hstore;
6 SELECT 'aa => {a,aaa}, qq=>{ a=>12, b=>16 , c=> { c1, c2}, d=>{d1=>d1, d2=>d2, d1=>d3} }'::hstore;
8 SELECT '"aa"=>{a,aaa}, "qq"=>{"a"=>"12", "b"=>"16", "c"=>{c1,c2}, "d"=>{"d1"=>"d1", "d2"=>"d2"}}'::hstore;
10 SELECT '"aa"=>{a,aaa}, "qq"=>{"a"=>"12", "b"=>"16", "c"=>{c1,c2,{c3},{c4=>4}}, "d"=>{"d1"=>"d1", "d2"=>"d2"}}'::hstore;
12 SELECT 'ff => {a,aaa}'::hstore;
15 select 'null'::hstore;
16 select '{null}'::hstore;
20 --test optional outer braces
21 SELECT 'a=>1'::hstore;
22 SELECT '{a=>1}'::hstore;
23 SELECT '{a,b}'::hstore;
24 SELECT '{a,{b}}'::hstore;
25 SELECT '{{a},b}'::hstore;
26 SELECT '{a,{b},{c}}'::hstore;
27 SELECT '{{a},{b},c}'::hstore;
28 SELECT '{{a},b,{c}}'::hstore;
29 SELECT '{a,{b=>1}}'::hstore;
30 SELECT '{{a},{b=>1}}'::hstore;
38 SELECT hstore_to_json('a=>1');
39 SELECT hstore_to_json('{a=>1}');
40 SELECT hstore_to_json('{a,b}');
41 SELECT hstore_to_json('{a,{b}}');
42 SELECT hstore_to_json('{{a},b}');
43 SELECT hstore_to_json('{a,{b},{c}}');
44 SELECT hstore_to_json('{{a},{b},c}');
45 SELECT hstore_to_json('{{a},b,{c}}');
46 SELECT hstore_to_json('{a,{b=>1}}');
47 SELECT hstore_to_json('{{a},{b=>1}}');
48 SELECT hstore_to_json('{{a},{b=>1},{c}}');
49 SELECT hstore_to_json('a');
50 SELECT hstore_to_json('{a}');
51 SELECT hstore_to_json('');
52 SELECT hstore_to_json('{}');
54 SELECT hstore_to_json('"aa"=>{a,aaa}, "qq"=>{"a"=>"12", "b"=>"16", "c"=>{c1,c2,{c3},{c4=>4}}, "d"=>{"d1"=>"d1", "d2"=>"d2"}}'::hstore);
58 SELECT 'ff => {a=>12, b=>16}, qq=> 123, x=>{1,2}, Y=>NULL'::hstore -> 'ff',
59 'ff => {a=>12, b=>16}, qq=> 123, x=>{1,2}, Y=>NULL'::hstore -> 'qq',
60 ('ff => {a=>12, b=>16}, qq=> 123, x=>{1,2}, Y=>NULL'::hstore -> 'Y') IS NULL AS t,
61 'ff => {a=>12, b=>16}, qq=> 123, x=>{1,2}, Y=>NULL'::hstore -> 'x';
63 SELECT '[ a, b, c, d]'::hstore -> 'a';
66 CREATE TABLE testtype (i int, h hstore, a int[], j json);
67 INSERT INTO testtype VALUES (1, 'a=>1', '{1,2,3}', '{"x": 2}');
69 SELECT populate_record(v, 'i=>2'::hstore) FROM testtype v;
70 SELECT populate_record(v, 'i=>2, a=>{7,8,9}'::hstore) FROM testtype v;
71 SELECT populate_record(v, 'i=>2, h=>{b=>3}, a=>{7,8,9}'::hstore) FROM testtype v;
72 SELECT populate_record(v, 'i=>2, h=>{b=>3}, a=>{7,8,9}, j=>{a=>{1,2,3}}'::hstore) FROM testtype v;
76 SELECT 'b=>{a,c}'::hstore - 'a'::text;
77 SELECT 'b=>{a,c}, a=>1'::hstore - 'a'::text;
78 SELECT 'b=>{a,c}, a=>[2,3]'::hstore - 'a'::text;
79 SELECT 'b=>{a,c}, a=>[2,3]'::hstore - 'a'::text;
80 SELECT '[2,3,a]'::hstore - 'a'::text;
81 SELECT '[a,2,3,a]'::hstore - 'a'::text;
82 SELECT '[a,a]'::hstore - 'a'::text;
83 SELECT '[a]'::hstore - 'a'::text;
84 SELECT 'a=>1'::hstore - 'a'::text;
85 SELECT ''::hstore - 'a'::text;
87 SELECT '{a, 1 , b,2, c,3}'::hstore - ARRAY['d','b'];
89 SELECT '{a=>{1,2}, v=>23, b=>c}'::hstore - 'v'::hstore;
90 SELECT '{a=>{1,2}, v=>23, b=>c}'::hstore - 'v=>23'::hstore;
91 SELECT '{a=>{1,2}, v=>23, b=>c}'::hstore - 'v=>{1,2}'::hstore;
92 SELECT '{a=>{1,2}, v=>23, b=>c}'::hstore - 'a=>{1,2}'::hstore;
93 SELECT '{a, {1,2}, v, 23, b, c}'::hstore - 'v'::hstore;
94 SELECT '{a, {1,2}, v, 23, b, c}'::hstore - 'v=>23'::hstore;
95 SELECT '{a, {1,2}, v, 23, b, c}'::hstore - '[v,23]'::hstore;
96 SELECT '{a, {1,2}, v, 23, b, c}'::hstore - '[v,{1,2}]'::hstore;
100 SELECT 'aa=>1 , b=>2, cq=>3'::hstore || '{cq,l, b,g, fg,f, 1,2}'::hstore;
101 SELECT '{aa,1 , b,2, cq,3}'::hstore || '{cq,l, b,g, fg,f, 1,2}'::hstore;
102 SELECT 'x'::hstore || 'a=>"1"':: hstore;
105 SELECT slice_array(hstore 'aa=>1, b=>2, c=>3', ARRAY['g','h','i']);
106 SELECT slice_array(hstore '{aa,1, b,2, c,3}', ARRAY['g','h','i']);
107 SELECT slice_array(hstore 'aa=>1, b=>2, c=>3', ARRAY['b','c']);
108 SELECT slice_array(hstore '{aa,1, b,2, c,3}', ARRAY['b','c']);
109 SELECT slice_array(hstore 'aa=>1, b=>{2=>1}, c=>{1,2}', ARRAY['b','c']);
111 SELECT slice(hstore '{aa=>1, b=>2, c=>3}', ARRAY['g','h','i']);
112 SELECT slice(hstore '{aa,1, b,2, c,3}', ARRAY['g','h','i']);
113 SELECT slice(hstore '{aa=>1, b=>2, c=>3}', ARRAY['b','c']);
114 SELECT slice(hstore '{aa,1, b,2, c,3}', ARRAY['b','c']);
115 SELECT slice(hstore '{aa=>1, b=>{2=>1}, c=>{1,2}}', ARRAY['b','c']);
118 SELECT %% 'aa=>1, cq=>l, b=>{a,n}, fg=>NULL';
119 SELECT %% '{aa,1, cq,l, b,g, fg,NULL}';
120 SELECT hstore_to_matrix( 'aa=>1, cq=>l, b=>{a,n}, fg=>NULL');
121 SELECT hstore_to_matrix( '{aa,1, cq,l, b,g, fg,NULL}');
125 SELECT 'a=>b'::hstore @> 'a=>b, c=>b';
126 SELECT 'a=>b, c=>b'::hstore @> 'a=>b';
127 SELECT 'a=>{1,2}, c=>b'::hstore @> 'a=>{1,2}';
128 SELECT 'a=>{2,1}, c=>b'::hstore @> 'a=>{1,2}';
129 SELECT 'a=>{1=>2}, c=>b'::hstore @> 'a=>{1,2}';
130 SELECT 'a=>{2=>1}, c=>b'::hstore @> 'a=>{1,2}';
131 SELECT 'a=>{1=>2}, c=>b'::hstore @> 'a=>{1=>2}';
132 SELECT 'a=>{2=>1}, c=>b'::hstore @> 'a=>{1=>2}';
133 SELECT '{a,b}'::hstore @> '{a,b, c,b}';
134 SELECT '{a,b, c,b}'::hstore @> '{a,b}';
135 SELECT '{a,b, c,{1,2}}'::hstore @> '{a,{1,2}}';
136 SELECT '{a,b, c,{1,2}}'::hstore @> '{b,{1,2}}';
138 SELECT 'a=>{1,2}, c=>b'::hstore @> 'a=>{1}';
139 SELECT 'a=>{1,2}, c=>b'::hstore @> 'a=>{2}';
140 SELECT 'a=>{1,2}, c=>b'::hstore @> 'a=>{3}';
141 SELECT 'a=>{1,2,{c=>3, x=>4}}, c=>b'::hstore @> 'a=>{{c=>3}}';
142 SELECT 'a=>{1,2,{c=>3, x=>4}}, c=>b'::hstore @> 'a=>{{x=>4}}';
143 SELECT 'a=>{1,2,{c=>3, x=>4}}, c=>b'::hstore @> 'a=>{{x=>4},3}';
144 SELECT 'a=>{1,2,{c=>3, x=>4}}, c=>b'::hstore @> 'a=>{{x=>4},1}';
148 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'n';
149 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'a';
150 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'b';
151 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'c';
152 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'd';
153 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 'd' %> '1';
155 SELECT '[1,2,3,{a,b}]'::hstore %> '1';
156 SELECT '["1",2,3,{a,b}]'::hstore %> '1';
158 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 5;
159 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 4;
160 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 3;
161 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 2;
162 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 1;
163 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore %> 0;
165 SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 5;
166 SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 4;
167 SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 3;
168 SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 2;
169 SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 1;
170 SELECT '[a,b, c,{1,2}, NULL]'::hstore %> 0;
173 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 5;
174 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 4;
175 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 3;
176 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 2;
177 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 1;
178 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> 0;
180 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 5;
181 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 4;
182 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 3;
183 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 2;
184 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 1;
185 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> 0;
187 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -6;
188 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -5;
189 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -4;
190 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -3;
191 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -2;
192 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore -> -1;
194 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -6;
195 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -5;
196 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -4;
197 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -3;
198 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -2;
199 SELECT '[a,b, c,{1,2}, NULL]'::hstore -> -1;
203 SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{0}';
204 SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{a}';
205 SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c}';
206 SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, 0}';
207 SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, 1}';
208 SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, 2}';
209 SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, 3}';
210 SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, -1}';
211 SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, -2}';
212 SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, -3}';
213 SELECT 'a=>b, c=>{1,2,3}'::hstore #> '{c, -4}';
215 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #> '{0}';
216 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #> '{3}';
217 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #> '{4}';
218 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #> '{4,5}';
222 SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{0}';
223 SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{a}';
224 SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c}';
225 SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, 0}';
226 SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, 1}';
227 SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, 2}';
228 SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, 3}';
229 SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, -1}';
230 SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, -2}';
231 SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, -3}';
232 SELECT 'a=>b, c=>{1,2,3}'::hstore #%> '{c, -4}';
234 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #%> '{0}';
235 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #%> '{3}';
236 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #%> '{4}';
237 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #%> '{4,5}';
241 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 5;
242 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 4;
243 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 3;
244 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 2;
245 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 1;
246 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? 0;
248 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 5;
249 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 4;
250 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 3;
251 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 2;
252 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 1;
253 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? 0;
255 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -6;
256 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -5;
257 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -4;
258 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -3;
259 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -2;
260 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore ? -1;
262 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -6;
263 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -5;
264 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -4;
265 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -3;
266 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -2;
267 SELECT '[a,b, c,{1,2}, NULL]'::hstore ? -1;
269 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{0}'::text[];
270 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{a}'::text[];
271 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c}'::text[];
272 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{b}'::text[];
273 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, 0}'::text[];
274 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, 1}'::text[];
275 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, 2}'::text[];
276 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, 3}'::text[];
277 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, -1}'::text[];
278 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, -2}'::text[];
279 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, -3}'::text[];
280 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, -4}'::text[];
281 SELECT 'a=>b, c=>{1,2,3}'::hstore #? '{c, -5}'::text[];
283 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #? '{0}'::text[];
284 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #? '{3}'::text[];
285 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #? '{4}'::text[];
286 SELECT '[0, 1, 2, {3,4}, {5=>five}]'::hstore #? '{4,5}'::text[];
290 SELECT 'a=>1'::hstore #- '{x}';
291 SELECT 'a=>1'::hstore #- '{a}';
292 SELECT 'a=>1'::hstore #- '{NULL}';
293 SELECT 'a=>1, b=>2, c=>3'::hstore #- '{x}';
294 SELECT 'a=>1, b=>2, c=>3'::hstore #- '{a}';
295 SELECT 'a=>1, b=>2, c=>3'::hstore #- '{b}';
296 SELECT 'a=>1, b=>2, c=>3'::hstore #- '{c}';
297 SELECT 'a=>1'::hstore #- '{x,1}';
298 SELECT 'a=>1'::hstore #- '{a,1}';
299 SELECT 'a=>1'::hstore #- '{NULL,1}';
300 SELECT 'a=>1, b=>2, c=>3'::hstore #- '{x,1}';
301 SELECT 'a=>1, b=>2, c=>3'::hstore #- '{a,1}';
302 SELECT 'a=>1, b=>2, c=>3'::hstore #- '{b,1}';
303 SELECT 'a=>1, b=>2, c=>3'::hstore #- '{c,1}';
305 SELECT '[a]'::hstore #- '{2}';
306 SELECT '[a]'::hstore #- '{1}';
307 SELECT '[a]'::hstore #- '{0}';
308 SELECT '[a]'::hstore #- '{-1}';
309 SELECT '[a]'::hstore #- '{-2}';
311 SELECT '[a,b,c]'::hstore #- '{3}';
312 SELECT '[a,b,c]'::hstore #- '{2}';
313 SELECT '[a,b,c]'::hstore #- '{1}';
314 SELECT '[a,b,c]'::hstore #- '{0}';
315 SELECT '[a,b,c]'::hstore #- '{-1}';
316 SELECT '[a,b,c]'::hstore #- '{-2}';
317 SELECT '[a,b,c]'::hstore #- '{-3}';
318 SELECT '[a,b,c]'::hstore #- '{-4}';
320 SELECT '[a,b,c]'::hstore #- '{0,0}';
322 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{x}';
323 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{a}';
324 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{b}';
325 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{c}';
326 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d}';
328 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{b, 0}';
329 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{b, -1}';
330 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{b, -1}' #- '{b, -1}';
331 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{c, 1}';
332 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{c, 2}';
333 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d, 1, -2}';
334 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d, 1, 1}';
335 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d, 1, 0}';
336 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d, 1, 0}' #- '{d, 1, 0}';
337 SELECT 'n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore #- '{d, 1, 0}' #- '{d, 1, 0}' #- '{d, 1, 0}';
341 SELECT '[a,b,c]'::hstore - 3;
342 SELECT '[a,b,c]'::hstore - 2;
343 SELECT '[a,b,c]'::hstore - 1;
344 SELECT '[a,b,c]'::hstore - 0;
345 SELECT '[a,b,c]'::hstore - -1;
346 SELECT '[a,b,c]'::hstore - -2;
347 SELECT '[a,b,c]'::hstore - -3;
348 SELECT '[a,b,c]'::hstore - -4;
350 SELECT 'a=>1, b=>2, c=>3'::hstore - 3;
351 SELECT 'a=>1, b=>2, c=>3'::hstore - 2;
352 SELECT 'a=>1, b=>2, c=>3'::hstore - 1;
353 SELECT 'a=>1, b=>2, c=>3'::hstore - 0;
354 SELECT 'a=>1, b=>2, c=>3'::hstore - -1;
355 SELECT 'a=>1, b=>2, c=>3'::hstore - -2;
356 SELECT 'a=>1, b=>2, c=>3'::hstore - -3;
357 SELECT 'a=>1, b=>2, c=>3'::hstore - -4;
361 SELECT replace('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{n}', '{1,2,3}');
362 SELECT replace('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{b,-1}', '{1,2,3}');
363 SELECT replace('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{d,1,0}', '{1,2,3}');
364 SELECT replace('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{d,NULL,0}', '{1,2,3}');
368 SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{}', 'n=>not_null');
369 SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{n}', 'n=>not_null');
370 SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{n}', 'not_null');
371 SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{n}', '{not_null}');
372 SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{}', 'b=>{3,4}');
373 SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{b}', '{3,4}');
374 SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{d,1}', '{4,5}');
375 SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{d,1}', '4=>5');
376 SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{d}', '2=>{4,5}');
377 SELECT concat_path('n=>NULL, a=>1, b=>{1,2}, c=>{1=>2}, d=>{1=>{2,3}}'::hstore, '{NULL,1}', '4=>5');
378 SELECT concat_path('x'::hstore, '{}'::text[], 'a=>"1"':: hstore);
382 SELECT ('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::text)::hstore AS err;
383 SELECT ('{"f2":{"f3":1},"f4":{"f5":99,"f6":"stringy"}}'::json)::hstore AS ok;
387 SELECT q->'tags' FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore) AS q;
389 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{}') AS q;
390 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-3}') AS q;
391 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-2}') AS q;
392 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-1}') AS q;
393 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{0}') AS q;
394 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{1}') AS q;
395 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{2}') AS q;
396 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{NULL}') AS q;
397 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-3,tags}') AS q;
398 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-2,tags}') AS q;
399 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-1,tags}') AS q;
400 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{0,tags}') AS q;
401 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{1,tags}') AS q;
402 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{2,tags}') AS q;
403 SELECT q FROM hvals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{NULL,tags}') AS q;
405 SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{}') AS q;
406 SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{1}') AS q;
407 SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{a}') AS q;
408 SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{b}') AS q;
409 SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{c}') AS q;
410 SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL}') AS q;
411 SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{a,c}') AS q;
412 SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,c}') AS q;
413 SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{b,NULL}') AS q;
414 SELECT q FROM hvals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,1}') AS q;
415 SELECT q FROM hvals('a=>{b=>c, c=>b, 1=>first}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,1}') AS q;
419 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{}') AS q;
420 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-3}') AS q;
421 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-2}') AS q;
422 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-1}') AS q;
423 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{0}') AS q;
424 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{1}') AS q;
425 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{2}') AS q;
426 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{NULL}') AS q;
427 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-3,tags}') AS q;
428 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-2,tags}') AS q;
429 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{-1,tags}') AS q;
430 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{0,tags}') AS q;
431 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{1,tags}') AS q;
432 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{2,tags}') AS q;
433 SELECT q FROM svals('{{tags=>1, sh=>2}, {tags=>3, sh=>4}}'::hstore, '{NULL,tags}') AS q;
435 SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{}') AS q;
436 SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{1}') AS q;
437 SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{a}') AS q;
438 SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{b}') AS q;
439 SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{c}') AS q;
440 SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL}') AS q;
441 SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{a,c}') AS q;
442 SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,c}') AS q;
443 SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{b,NULL}') AS q;
444 SELECT q FROM svals('a=>{b=>c, c=>b}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,1}') AS q;
445 SELECT q FROM svals('a=>{b=>c, c=>b, 1=>first}, b=>{1,2}, c=>cc, 1=>first'::hstore, '{NULL,1}') AS q;
449 SELECT * FROM each('a=>b, c=>cc'::hstore) AS q;
450 SELECT * FROM each('[a, b, c, cc]'::hstore) AS q;
451 SELECT * FROM each('a=>{b=>c, c=>b, 1=>first}, b=>{1,2}, c=>cc, 1=>first, n=>null'::hstore) AS q;
453 SELECT * FROM each_hstore('a=>b, c=>cc'::hstore) AS q;
454 SELECT * FROM each_hstore('[a, b, c, cc]'::hstore) AS q;
455 SELECT * FROM each_hstore('a=>{b=>c, c=>b, 1=>first}, b=>{1,2}, c=>cc, 1=>first, n=>null'::hstore) AS q;
459 SELECT 'a=>1, b=>{c=>3}, d=>[4,[5]]'::hstore AS h, '[a, {b=>c}, [c, d, e]]'::hstore AS a;
461 SET hstore.pretty_print = true;
462 SELECT 'a=>1, b=>{c=>3}, d=>[4,[5]], e=>[1,2,3,4], f=>g, g=>j'::hstore AS h,
463 '[a, {b=>c, c=>d}, [c, d, e, [1,2], h, {f=>g, g=>f}]]'::hstore AS a;
464 RESET hstore.pretty_print;
466 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore);
467 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, loose := true );
468 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, root_hash_decorated := true );
469 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, array_curly_braces := true );
470 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, json := true );
472 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, json := true, loose := true );
473 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, json := true, root_hash_decorated := true );
474 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, json := true, array_curly_braces := true );
476 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, loose := true, root_hash_decorated := true );
477 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, loose := true, array_curly_braces := true );
479 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, root_hash_decorated := true, array_curly_braces := true );
480 SELECT hstore_print('a=>t, f=>t, t=>"f", arr=>[1,2,3,"3",x], 123=>string'::hstore, root_hash_decorated := true, array_curly_braces := true, loose := true);