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

Commit754148d

Browse files
committed
Add GIN support for pg_trgm. From Guillaume Smet <guillaume.smet@gmail.com>
with minor editorization by me.Hstore improvements* add operation hstore ? text - excat equivalent of exist()* remove undocumented behaviour of contains operation with NULL value* now 'key'::text=>NULL returns '"key"=>NULL' instead of NULL* Add GIN support for contains and exist operations* Add GiST support for exist operatiion* improve regression tests
1 parent15f91f2 commit754148d

File tree

11 files changed

+355
-59
lines changed

11 files changed

+355
-59
lines changed

‎contrib/hstore/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,11 @@
1-
# $PostgreSQL: pgsql/contrib/hstore/Makefile,v 1.4 2007/02/09 17:24:33 petere Exp $
1+
# $PostgreSQL: pgsql/contrib/hstore/Makefile,v 1.5 2007/03/14 14:21:52 teodor Exp $
22

33
subdir = contrib/hstore
44
top_builddir = ../..
55
include$(top_builddir)/src/Makefile.global
66

77
MODULE_big = hstore
8-
OBJS = hstore_io.o hstore_op.o hstore_gist.o crc32.o
8+
OBJS = hstore_io.o hstore_op.o hstore_gist.ohstore_gin.ocrc32.o
99

1010
DATA_built = hstore.sql
1111
DATA = uninstall_hstore.sql

‎contrib/hstore/README.hstore

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -117,13 +117,14 @@ regression=# select * from each('a=>1,b=>2');
117117
a | 1
118118
b | 2
119119

120-
* exist (hstore,text) - returns 'true if key is exists in hstore and
121-
false otherwise.
120+
* exist (hstore,text)
121+
* hstore ? text
122+
- returns 'true if key is exists in hstore and false otherwise.
122123

123-
regression=# select exist('a=>1','a');
124-
exist
125-
----------
126-
t
124+
regression=# select exist('a=>1','a'), 'a=>1' ? 'a';
125+
exist| ?column?
126+
-------+----------
127+
t | t
127128

128129
* defined (hstore,text) - returns true if key is exists in hstore and
129130
its value is not NULL.
@@ -135,9 +136,10 @@ regression=# select defined('a=>NULL','a');
135136

136137
Indices
137138

138-
Module provides index support for '@>' and '<@' operations.
139+
Module provides index support for '@>' and '?' operations.
139140

140141
create index hidx on testhstore using gist(h);
142+
create index hidx on testhstore using gin(h);
141143

142144
Note
143145

‎contrib/hstore/data/hstore.data

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -998,3 +998,4 @@ auth=>BC, title=>CAC, subtitle=>BA, line=>997, date=>BAA
998998
wait=>AB, user=>ABC, line=>998, pos=>41, node=>CAC
999999
state=>4, title=>AC, bad=>t, status=>59, line=>999, disabled=>t
10001000
user=>BC, line=>1000
1001+
wait=>NULL, line=>1000

‎contrib/hstore/expected/hstore.out

Lines changed: 77 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -272,6 +272,12 @@ select ('aa=>NULL, c=>d , b=>16'::hstore->'aa') is null;
272272
t
273273
(1 row)
274274

275+
select ('aa=>"NULL", c=>d , b=>16'::hstore->'aa') is null;
276+
?column?
277+
----------
278+
f
279+
(1 row)
280+
275281
-- exists/defined
276282
select exist('a=>NULL, b=>qq', 'a');
277283
exist
@@ -291,6 +297,12 @@ select exist('a=>NULL, b=>qq', 'c');
291297
f
292298
(1 row)
293299

300+
select exist('a=>"NULL", b=>qq', 'a');
301+
exist
302+
-------
303+
t
304+
(1 row)
305+
294306
select defined('a=>NULL, b=>qq', 'a');
295307
defined
296308
---------
@@ -309,6 +321,12 @@ select defined('a=>NULL, b=>qq', 'c');
309321
f
310322
(1 row)
311323

324+
select defined('a=>"NULL", b=>qq', 'a');
325+
defined
326+
---------
327+
t
328+
(1 row)
329+
312330
-- delete
313331
select delete('a=>1 , b=>2, c=>3'::hstore, 'a');
314332
delete
@@ -384,6 +402,18 @@ select 'a=>g, b=>c'::hstore || ( 'b'=>'gf' );
384402
"a"=>"g", "b"=>"gf"
385403
(1 row)
386404

405+
select 'a=>g, b=>c'::hstore || ( 'b'=>'NULL' );
406+
?column?
407+
-----------------------
408+
"a"=>"g", "b"=>"NULL"
409+
(1 row)
410+
411+
select 'a=>g, b=>c'::hstore || ( 'b'=>NULL );
412+
?column?
413+
---------------------
414+
"a"=>"g", "b"=>NULL
415+
(1 row)
416+
387417
-- keys/values
388418
select akeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
389419
akeys
@@ -485,19 +515,19 @@ select * from each('aaa=>bq, b=>NULL, ""=>1 ');
485515
(3 rows)
486516

487517
-- @>
488-
select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>NULL';
518+
select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b';
489519
?column?
490520
----------
491521
t
492522
(1 row)
493523

494-
select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>NULL, c=>NULL';
524+
select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>NULL';
495525
?column?
496526
----------
497527
t
498528
(1 row)
499529

500-
select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>NULL, g=>NULL';
530+
select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, g=>NULL';
501531
?column?
502532
----------
503533
f
@@ -521,12 +551,6 @@ select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b';
521551
t
522552
(1 row)
523553

524-
select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>NULL';
525-
?column?
526-
----------
527-
t
528-
(1 row)
529-
530554
select 'a=>b, b=>1, c=>NULL'::hstore @> 'a=>b, c=>q';
531555
?column?
532556
----------
@@ -538,7 +562,7 @@ CREATE TABLE testhstore (h hstore);
538562
select count(*) from testhstore where h @> 'wait=>NULL';
539563
count
540564
-------
541-
189
565+
1
542566
(1 row)
543567

544568
select count(*) from testhstore where h @> 'wait=>CC';
@@ -553,12 +577,18 @@ select count(*) from testhstore where h @> 'wait=>CC, public=>t';
553577
2
554578
(1 row)
555579

580+
select count(*) from testhstore where h ? 'public';
581+
count
582+
-------
583+
194
584+
(1 row)
585+
556586
create index hidx on testhstore using gist(h);
557587
set enable_seqscan=off;
558588
select count(*) from testhstore where h @> 'wait=>NULL';
559589
count
560590
-------
561-
189
591+
1
562592
(1 row)
563593

564594
select count(*) from testhstore where h @> 'wait=>CC';
@@ -573,26 +603,59 @@ select count(*) from testhstore where h @> 'wait=>CC, public=>t';
573603
2
574604
(1 row)
575605

606+
select count(*) from testhstore where h ? 'public';
607+
count
608+
-------
609+
194
610+
(1 row)
611+
612+
drop index hidx;
613+
create index hidx on testhstore using gin (h);
614+
set enable_seqscan=off;
615+
select count(*) from testhstore where h @> 'wait=>NULL';
616+
count
617+
-------
618+
1
619+
(1 row)
620+
621+
select count(*) from testhstore where h @> 'wait=>CC';
622+
count
623+
-------
624+
15
625+
(1 row)
626+
627+
select count(*) from testhstore where h @> 'wait=>CC, public=>t';
628+
count
629+
-------
630+
2
631+
(1 row)
632+
633+
select count(*) from testhstore where h ? 'public';
634+
count
635+
-------
636+
194
637+
(1 row)
638+
576639
select count(*) from (select (each(h)).key from testhstore) as wow ;
577640
count
578641
-------
579-
4779
642+
4781
580643
(1 row)
581644

582645
select key, count(*) from (select (each(h)).key from testhstore) as wow group by key order by count desc, key;
583646
key | count
584647
-----------+-------
585-
line |883
648+
line |884
586649
query | 207
587650
pos | 203
588651
node | 202
589652
space | 197
590653
status | 195
591654
public | 194
592655
title | 190
656+
wait | 190
593657
org | 189
594658
user | 189
595-
wait | 189
596659
coauthors | 188
597660
disabled | 185
598661
indexed | 184

‎contrib/hstore/hstore.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -50,4 +50,7 @@ typedef struct
5050
intcomparePairs(constvoid*a,constvoid*b);
5151
intuniquePairs(Pairs*a,int4l,int4*buflen);
5252

53+
#defineHStoreContainsStrategyNumber 7
54+
#defineHStoreExistsStrategyNumber 9
55+
5356
#endif

‎contrib/hstore/hstore.sql.in

Lines changed: 40 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -40,6 +40,14 @@ RETURNS bool
4040
AS 'MODULE_PATHNAME','exists'
4141
LANGUAGE 'C' with (isstrict,iscachable);
4242

43+
CREATE OPERATOR ? (
44+
LEFTARG = hstore,
45+
RIGHTARG = text,
46+
PROCEDURE = exist,
47+
RESTRICT = contsel,
48+
JOIN = contjoinsel
49+
);
50+
4351
CREATE FUNCTION isdefined(hstore,text)
4452
RETURNS bool
4553
AS 'MODULE_PATHNAME','defined'
@@ -116,7 +124,7 @@ CREATE OPERATOR ~ (
116124
CREATE FUNCTION tconvert(text,text)
117125
RETURNS hstore
118126
AS 'MODULE_PATHNAME'
119-
LANGUAGE 'C' with (isstrict,iscachable);
127+
LANGUAGE 'C' with (iscachable);
120128

121129
CREATE OPERATOR => (
122130
LEFTARG = text,
@@ -210,7 +218,8 @@ LANGUAGE 'C';
210218
CREATE OPERATOR CLASS gist_hstore_ops
211219
DEFAULT FOR TYPE hstore USING gist
212220
AS
213-
OPERATOR 7 @> RECHECK,
221+
OPERATOR 7 @> RECHECK,
222+
OPERATOR 9 ?(hstore,text) RECHECK,
214223
--OPERATOR 8 <@ RECHECK,
215224
OPERATOR 13 @ RECHECK,
216225
--OPERATOR 14 ~ RECHECK,
@@ -223,4 +232,33 @@ AS
223232
FUNCTION 7 ghstore_same (internal, internal, internal),
224233
STORAGE ghstore;
225234

235+
-- define the GIN support methods
236+
237+
CREATE FUNCTION gin_extract_hstore(internal, internal)
238+
RETURNS internal
239+
AS 'MODULE_PATHNAME'
240+
LANGUAGE C;
241+
242+
CREATE FUNCTION gin_extract_hstore_query(internal, internal, int2)
243+
RETURNS internal
244+
AS 'MODULE_PATHNAME'
245+
LANGUAGE C;
246+
247+
CREATE FUNCTION gin_consistent_hstore(internal, int2, internal)
248+
RETURNS internal
249+
AS 'MODULE_PATHNAME'
250+
LANGUAGE C;
251+
252+
CREATE OPERATOR CLASS gin_hstore_ops
253+
DEFAULT FOR TYPE hstore USING gin
254+
AS
255+
OPERATOR 7 @> RECHECK,
256+
OPERATOR 9 ?(hstore,text),
257+
FUNCTION 1 bttextcmp(text,text),
258+
FUNCTION 2 gin_extract_hstore(internal, internal),
259+
FUNCTION 3 gin_extract_hstore_query(internal, internal, int2),
260+
FUNCTION 4 gin_consistent_hstore(internal, int2, internal),
261+
STORAGE text;
262+
263+
226264
END;

0 commit comments

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