Add hstore contrib module.

Per discussion
http://archives.postgresql.org/pgsql-hackers/2006-08/msg01409.php

1 files changed, 131 insertions, 0 deletions

diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql
new file mode 100644
index 0000000000..298ffb0893
--- /dev/null
+++ b/contrib/hstore/sql/hstore.sql
@@ -0,0 +1,131 @@
+\set ECHO none
+\i hstore.sql
+set escape_string_warning=off;
+\set ECHO all
+--hstore;
+
+select ''::hstore;
+select 'a=>b'::hstore;
+select ' a=>b'::hstore;
+select 'a =>b'::hstore;
+select 'a=>b '::hstore;
+select 'a=> b'::hstore;
+select '"a"=>"b"'::hstore;
+select ' "a"=>"b"'::hstore;
+select '"a" =>"b"'::hstore;
+select '"a"=>"b" '::hstore;
+select '"a"=> "b"'::hstore;
+select 'aa=>bb'::hstore;
+select ' aa=>bb'::hstore;
+select 'aa =>bb'::hstore;
+select 'aa=>bb '::hstore;
+select 'aa=> bb'::hstore;
+select '"aa"=>"bb"'::hstore;
+select ' "aa"=>"bb"'::hstore;
+select '"aa" =>"bb"'::hstore;
+select '"aa"=>"bb" '::hstore;
+select '"aa"=> "bb"'::hstore;
+
+select 'aa=>bb, cc=>dd'::hstore;
+select 'aa=>bb , cc=>dd'::hstore;
+select 'aa=>bb ,cc=>dd'::hstore;
+select 'aa=>bb, "cc"=>dd'::hstore;
+select 'aa=>bb , "cc"=>dd'::hstore;
+select 'aa=>bb ,"cc"=>dd'::hstore;
+select 'aa=>"bb", cc=>dd'::hstore;
+select 'aa=>"bb" , cc=>dd'::hstore;
+select 'aa=>"bb" ,cc=>dd'::hstore;
+
+select 'aa=>null'::hstore;
+select 'aa=>NuLl'::hstore;
+select 'aa=>"NuLl"'::hstore;
+
+select '\\=a=>q=w'::hstore;
+select '"=a"=>q\\=w'::hstore;
+select '"\\"a"=>q>w'::hstore;
+select '\\"a=>q"w'::hstore;
+
+select ''::hstore;
+select '	'::hstore;
+
+-- -> operator
+
+select 'aa=>b, c=>d , b=>16'::hstore->'c';
+select 'aa=>b, c=>d , b=>16'::hstore->'b';
+select 'aa=>b, c=>d , b=>16'::hstore->'aa';
+select ('aa=>b, c=>d , b=>16'::hstore->'gg') is null;
+select ('aa=>NULL, c=>d , b=>16'::hstore->'aa') is null;
+
+-- exists/defined
+
+select isexists('a=>NULL, b=>qq', 'a');
+select isexists('a=>NULL, b=>qq', 'b');
+select isexists('a=>NULL, b=>qq', 'c');
+select isdefined('a=>NULL, b=>qq', 'a');
+select isdefined('a=>NULL, b=>qq', 'b');
+select isdefined('a=>NULL, b=>qq', 'c');
+
+-- delete 
+
+select delete('a=>1 , b=>2, c=>3'::hstore, 'a');
+select delete('a=>null , b=>2, c=>3'::hstore, 'a');
+select delete('a=>1 , b=>2, c=>3'::hstore, 'b');
+select delete('a=>1 , b=>2, c=>3'::hstore, 'c');
+select delete('a=>1 , b=>2, c=>3'::hstore, 'd');
+
+-- ||
+select 'aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f';
+select 'aa=>1 , b=>2, cq=>3'::hstore || 'aq=>l';
+select 'aa=>1 , b=>2, cq=>3'::hstore || 'aa=>l';
+select 'aa=>1 , b=>2, cq=>3'::hstore || '';
+select ''::hstore || 'cq=>l, b=>g, fg=>f';
+
+-- =>
+select 'a=>g, b=>c'::hstore || ( 'asd'=>'gf' );
+select 'a=>g, b=>c'::hstore || ( 'b'=>'gf' );
+
+-- keys/values
+select akeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
+select akeys('""=>1');
+select akeys('');
+select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
+select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL');
+select avals('""=>1');
+select avals('');
+
+select * from skeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
+select * from skeys('""=>1');
+select * from skeys('');
+select * from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f');
+select *, svals is null from svals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL');
+select * from svals('""=>1');
+select * from svals('');
+
+select * from each('aaa=>bq, b=>NULL, ""=>1 ');
+
+-- @
+select 'a=>b, b=>1, c=>NULL'::hstore @ 'a=>NULL';
+select 'a=>b, b=>1, c=>NULL'::hstore @ 'a=>NULL, c=>NULL';
+select 'a=>b, b=>1, c=>NULL'::hstore @ 'a=>NULL, g=>NULL';
+select 'a=>b, b=>1, c=>NULL'::hstore @ 'g=>NULL';
+select 'a=>b, b=>1, c=>NULL'::hstore @ 'a=>c';
+select 'a=>b, b=>1, c=>NULL'::hstore @ 'a=>b';
+select 'a=>b, b=>1, c=>NULL'::hstore @ 'a=>b, c=>NULL';
+select 'a=>b, b=>1, c=>NULL'::hstore @ 'a=>b, c=>q';
+
+CREATE TABLE testhstore (h hstore);
+\copy testhstore from 'data/hstore.data'
+
+select count(*) from testhstore where h @ 'wait=>NULL';
+select count(*) from testhstore where h @ 'wait=>CC';
+select count(*) from testhstore where h @ 'wait=>CC, public=>t';
+
+create index hidx on testhstore using gist(h);
+set enable_seqscan=off;
+
+select count(*) from testhstore where h @ 'wait=>NULL';
+select count(*) from testhstore where h @ 'wait=>CC';
+select count(*) from testhstore where h @ 'wait=>CC, public=>t';
+
+select count(*) from (select (each(h)).key from testhstore) as wow ;
+select key, count(*) from (select (each(h)).key from testhstore) as wow group by key order by count desc, key;