From 172eacba43417c31e5eb61a5ae7a6aaca7a25928 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 30 Sep 2009 19:50:22 +0000 Subject: [PATCH] Assorted improvements in contrib/hstore. Remove the 64K limit on the lengths of keys and values within an hstore. (This changes the on-disk format, but the old format can still be read.) Add support for btree/hash opclasses for hstore --- this is not so much for actual indexing purposes as to allow use of GROUP BY, DISTINCT, etc. Add various other new functions and operators. Andrew Gierth --- contrib/hstore/Makefile | 5 +- contrib/hstore/expected/hstore.out | 795 ++++++++++++++++- contrib/hstore/hstore.h | 188 +++- contrib/hstore/hstore.sql.in | 338 ++++++- contrib/hstore/hstore_compat.c | 376 ++++++++ contrib/hstore/hstore_gin.c | 115 ++- contrib/hstore/hstore_gist.c | 96 +- contrib/hstore/hstore_io.c | 844 ++++++++++++++++-- contrib/hstore/hstore_op.c | 1281 ++++++++++++++++++++------- contrib/hstore/sql/hstore.sql | 184 ++++ contrib/hstore/uninstall_hstore.sql | 60 +- doc/src/sgml/hstore.sgml | 282 +++++- 12 files changed, 4002 insertions(+), 562 deletions(-) create mode 100644 contrib/hstore/hstore_compat.c diff --git a/contrib/hstore/Makefile b/contrib/hstore/Makefile index adb014d0d1..bb69d70805 100644 --- a/contrib/hstore/Makefile +++ b/contrib/hstore/Makefile @@ -1,11 +1,12 @@ -# $PostgreSQL: pgsql/contrib/hstore/Makefile,v 1.6 2007/11/10 23:59:51 momjian Exp $ +# $PostgreSQL: pgsql/contrib/hstore/Makefile,v 1.7 2009/09/30 19:50:22 tgl Exp $ subdir = contrib/hstore top_builddir = ../.. include $(top_builddir)/src/Makefile.global MODULE_big = hstore -OBJS = hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o crc32.o +OBJS = hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o hstore_compat.o \ + crc32.o DATA_built = hstore.sql DATA = uninstall_hstore.sql diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out index eee23d3a05..34db7f571e 100644 --- a/contrib/hstore/expected/hstore.out +++ b/contrib/hstore/expected/hstore.out @@ -278,6 +278,31 @@ select ('aa=>"NULL", c=>d , b=>16'::hstore->'aa') is null; f (1 row) +-- -> array operator +select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['aa','c']; + ?column? +------------ + {"NULL",d} +(1 row) + +select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['c','aa']; + ?column? +------------ + {d,"NULL"} +(1 row) + +select 'aa=>NULL, c=>d , b=>16'::hstore -> ARRAY['aa','c',null]; + ?column? +--------------- + {NULL,d,NULL} +(1 row) + +select 'aa=>1, c=>3, b=>2, d=>4'::hstore -> ARRAY[['b','d'],['aa','c']]; + ?column? +--------------- + {{2,4},{1,3}} +(1 row) + -- exists/defined select exist('a=>NULL, b=>qq', 'a'); exist @@ -327,6 +352,90 @@ select defined('a=>"NULL", b=>qq', 'a'); t (1 row) +select hstore 'a=>NULL, b=>qq' ? 'a'; + ?column? +---------- + t +(1 row) + +select hstore 'a=>NULL, b=>qq' ? 'b'; + ?column? +---------- + t +(1 row) + +select hstore 'a=>NULL, b=>qq' ? 'c'; + ?column? +---------- + f +(1 row) + +select hstore 'a=>"NULL", b=>qq' ? 'a'; + ?column? +---------- + t +(1 row) + +select hstore 'a=>NULL, b=>qq' ?| ARRAY['a','b']; + ?column? +---------- + t +(1 row) + +select hstore 'a=>NULL, b=>qq' ?| ARRAY['b','a']; + ?column? +---------- + t +(1 row) + +select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','a']; + ?column? +---------- + t +(1 row) + +select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','d']; + ?column? +---------- + f +(1 row) + +select hstore 'a=>NULL, b=>qq' ?| '{}'::text[]; + ?column? +---------- + f +(1 row) + +select hstore 'a=>NULL, b=>qq' ?& ARRAY['a','b']; + ?column? +---------- + t +(1 row) + +select hstore 'a=>NULL, b=>qq' ?& ARRAY['b','a']; + ?column? +---------- + t +(1 row) + +select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','a']; + ?column? +---------- + f +(1 row) + +select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','d']; + ?column? +---------- + f +(1 row) + +select hstore 'a=>NULL, b=>qq' ?& '{}'::text[]; + ?column? +---------- + f +(1 row) + -- delete select delete('a=>1 , b=>2, c=>3'::hstore, 'a'); delete @@ -358,6 +467,193 @@ select delete('a=>1 , b=>2, c=>3'::hstore, 'd'); "a"=>"1", "b"=>"2", "c"=>"3" (1 row) +select 'a=>1 , b=>2, c=>3'::hstore - 'a'::text; + ?column? +-------------------- + "b"=>"2", "c"=>"3" +(1 row) + +select 'a=>null , b=>2, c=>3'::hstore - 'a'::text; + ?column? +-------------------- + "b"=>"2", "c"=>"3" +(1 row) + +select 'a=>1 , b=>2, c=>3'::hstore - 'b'::text; + ?column? +-------------------- + "a"=>"1", "c"=>"3" +(1 row) + +select 'a=>1 , b=>2, c=>3'::hstore - 'c'::text; + ?column? +-------------------- + "a"=>"1", "b"=>"2" +(1 row) + +select 'a=>1 , b=>2, c=>3'::hstore - 'd'::text; + ?column? +------------------------------ + "a"=>"1", "b"=>"2", "c"=>"3" +(1 row) + +select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b'::text) + = pg_column_size('a=>1, b=>2'::hstore); + ?column? +---------- + t +(1 row) + +-- delete (array) +select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','e']); + delete +------------------------------ + "a"=>"1", "b"=>"2", "c"=>"3" +(1 row) + +select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','b']); + delete +-------------------- + "a"=>"1", "c"=>"3" +(1 row) + +select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['a','c']); + delete +---------- + "b"=>"2" +(1 row) + +select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY[['b'],['c'],['a']]); + delete +-------- + +(1 row) + +select delete('a=>1 , b=>2, c=>3'::hstore, '{}'::text[]); + delete +------------------------------ + "a"=>"1", "b"=>"2", "c"=>"3" +(1 row) + +select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','e']; + ?column? +------------------------------ + "a"=>"1", "b"=>"2", "c"=>"3" +(1 row) + +select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','b']; + ?column? +-------------------- + "a"=>"1", "c"=>"3" +(1 row) + +select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c']; + ?column? +---------- + "b"=>"2" +(1 row) + +select 'a=>1 , b=>2, c=>3'::hstore - ARRAY[['b'],['c'],['a']]; + ?column? +---------- + +(1 row) + +select 'a=>1 , b=>2, c=>3'::hstore - '{}'::text[]; + ?column? +------------------------------ + "a"=>"1", "b"=>"2", "c"=>"3" +(1 row) + +select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c']) + = pg_column_size('b=>2'::hstore); + ?column? +---------- + t +(1 row) + +select pg_column_size('a=>1 , b=>2, c=>3'::hstore - '{}'::text[]) + = pg_column_size('a=>1, b=>2, c=>3'::hstore); + ?column? +---------- + t +(1 row) + +-- delete (hstore) +select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>4, b=>2'::hstore); + delete +--------------------- + "c"=>"3", "aa"=>"1" +(1 row) + +select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>NULL, c=>3'::hstore); + delete +--------------------- + "b"=>"2", "aa"=>"1" +(1 row) + +select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>1, b=>2, c=>3'::hstore); + delete +-------- + +(1 row) + +select delete('aa=>1 , b=>2, c=>3'::hstore, 'b=>2'::hstore); + delete +--------------------- + "c"=>"3", "aa"=>"1" +(1 row) + +select delete('aa=>1 , b=>2, c=>3'::hstore, ''::hstore); + delete +------------------------------- + "b"=>"2", "c"=>"3", "aa"=>"1" +(1 row) + +select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>4, b=>2'::hstore; + ?column? +--------------------- + "c"=>"3", "aa"=>"1" +(1 row) + +select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>NULL, c=>3'::hstore; + ?column? +--------------------- + "b"=>"2", "aa"=>"1" +(1 row) + +select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>1, b=>2, c=>3'::hstore; + ?column? +---------- + +(1 row) + +select 'aa=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore; + ?column? +--------------------- + "c"=>"3", "aa"=>"1" +(1 row) + +select 'aa=>1 , b=>2, c=>3'::hstore - ''::hstore; + ?column? +------------------------------- + "b"=>"2", "c"=>"3", "aa"=>"1" +(1 row) + +select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore) + = pg_column_size('a=>1, c=>3'::hstore); + ?column? +---------- + t +(1 row) + +select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ''::hstore) + = pg_column_size('a=>1, b=>2, c=>3'::hstore); + ?column? +---------- + t +(1 row) + -- || select 'aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'; ?column? @@ -389,6 +685,33 @@ select ''::hstore || 'cq=>l, b=>g, fg=>f'; "b"=>"g", "cq"=>"l", "fg"=>"f" (1 row) +select pg_column_size(''::hstore || ''::hstore) = pg_column_size(''::hstore); + ?column? +---------- + t +(1 row) + +select pg_column_size('aa=>1'::hstore || 'b=>2'::hstore) + = pg_column_size('aa=>1, b=>2'::hstore); + ?column? +---------- + t +(1 row) + +select pg_column_size('aa=>1, b=>2'::hstore || ''::hstore) + = pg_column_size('aa=>1, b=>2'::hstore); + ?column? +---------- + t +(1 row) + +select pg_column_size(''::hstore || 'aa=>1, b=>2'::hstore) + = pg_column_size('aa=>1, b=>2'::hstore); + ?column? +---------- + t +(1 row) + -- => select 'a=>g, b=>c'::hstore || ( 'asd'=>'gf' ); ?column? @@ -414,6 +737,367 @@ select 'a=>g, b=>c'::hstore || ( 'b'=>NULL ); "a"=>"g", "b"=>NULL (1 row) +select ('a=>g, b=>c'::hstore || ( NULL=>'b' )) is null; + ?column? +---------- + t +(1 row) + +select pg_column_size(('b'=>'gf')) + = pg_column_size('b=>gf'::hstore); + ?column? +---------- + t +(1 row) + +select pg_column_size('a=>g, b=>c'::hstore || ('b'=>'gf')) + = pg_column_size('a=>g, b=>gf'::hstore); + ?column? +---------- + t +(1 row) + +-- => arrays +select ARRAY['a','b','asd'] => ARRAY['g','h','i']; + ?column? +-------------------------------- + "a"=>"g", "b"=>"h", "asd"=>"i" +(1 row) + +select ARRAY['a','b','asd'] => ARRAY['g','h',NULL]; + ?column? +--------------------------------- + "a"=>"g", "b"=>"h", "asd"=>NULL +(1 row) + +select ARRAY['z','y','x'] => ARRAY['1','2','3']; + ?column? +------------------------------ + "x"=>"3", "y"=>"2", "z"=>"1" +(1 row) + +select ARRAY['aaa','bb','c','d'] => ARRAY[null::text,null,null,null]; + ?column? +----------------------------------------------- + "c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL +(1 row) + +select ARRAY['aaa','bb','c','d'] => null; + ?column? +----------------------------------------------- + "c"=>NULL, "d"=>NULL, "bb"=>NULL, "aaa"=>NULL +(1 row) + +select hstore 'aa=>1, b=>2, c=>3' => ARRAY['g','h','i']; + ?column? +---------- + +(1 row) + +select hstore 'aa=>1, b=>2, c=>3' => ARRAY['c','b']; + ?column? +-------------------- + "b"=>"2", "c"=>"3" +(1 row) + +select hstore 'aa=>1, b=>2, c=>3' => ARRAY['aa','b']; + ?column? +--------------------- + "b"=>"2", "aa"=>"1" +(1 row) + +select hstore 'aa=>1, b=>2, c=>3' => ARRAY['c','b','aa']; + ?column? +------------------------------- + "b"=>"2", "c"=>"3", "aa"=>"1" +(1 row) + +select quote_literal('{}'::text[] => '{}'::text[]); + quote_literal +--------------- + '' +(1 row) + +select quote_literal('{}'::text[] => null); + quote_literal +--------------- + '' +(1 row) + +select ARRAY['a'] => '{}'::text[]; -- error +ERROR: arrays must have same bounds +select '{}'::text[] => ARRAY['a']; -- error +ERROR: arrays must have same bounds +select pg_column_size(ARRAY['a','b','asd'] => ARRAY['g','h','i']) + = pg_column_size('a=>g, b=>h, asd=>i'::hstore); + ?column? +---------- + t +(1 row) + +select pg_column_size(hstore 'aa=>1, b=>2, c=>3' => ARRAY['c','b']) + = pg_column_size('b=>2, c=>3'::hstore); + ?column? +---------- + t +(1 row) + +select pg_column_size(hstore 'aa=>1, b=>2, c=>3' => ARRAY['c','b','aa']) + = pg_column_size('aa=>1, b=>2, c=>3'::hstore); + ?column? +---------- + t +(1 row) + +-- array input +select '{}'::text[]::hstore; + hstore +-------- + +(1 row) + +select ARRAY['a','g','b','h','asd']::hstore; +ERROR: array must have even number of elements +select ARRAY['a','g','b','h','asd','i']::hstore; + array +-------------------------------- + "a"=>"g", "b"=>"h", "asd"=>"i" +(1 row) + +select ARRAY[['a','g'],['b','h'],['asd','i']]::hstore; + array +-------------------------------- + "a"=>"g", "b"=>"h", "asd"=>"i" +(1 row) + +select ARRAY[['a','g','b'],['h','asd','i']]::hstore; +ERROR: array must have two columns +select ARRAY[[['a','g'],['b','h'],['asd','i']]]::hstore; +ERROR: wrong number of array subscripts +select hstore('{}'::text[]); + hstore +-------- + +(1 row) + +select hstore(ARRAY['a','g','b','h','asd']); +ERROR: array must have even number of elements +select hstore(ARRAY['a','g','b','h','asd','i']); + hstore +-------------------------------- + "a"=>"g", "b"=>"h", "asd"=>"i" +(1 row) + +select hstore(ARRAY[['a','g'],['b','h'],['asd','i']]); + hstore +-------------------------------- + "a"=>"g", "b"=>"h", "asd"=>"i" +(1 row) + +select hstore(ARRAY[['a','g','b'],['h','asd','i']]); +ERROR: array must have two columns +select hstore(ARRAY[[['a','g'],['b','h'],['asd','i']]]); +ERROR: wrong number of array subscripts +select hstore('[0:5]={a,g,b,h,asd,i}'::text[]); + hstore +-------------------------------- + "a"=>"g", "b"=>"h", "asd"=>"i" +(1 row) + +select hstore('[0:2][1:2]={{a,g},{b,h},{asd,i}}'::text[]); + hstore +-------------------------------- + "a"=>"g", "b"=>"h", "asd"=>"i" +(1 row) + +-- records +select hstore(v) from (values (1, 'foo', 1.2, 3::float8)) v(a,b,c,d); + hstore +------------------------------------------------ + "f1"=>"1", "f2"=>"foo", "f3"=>"1.2", "f4"=>"3" +(1 row) + +create domain hstestdom1 as integer not null default 0; +create table testhstore0 (a integer, b text, c numeric, d float8); +create table testhstore1 (a integer, b text, c numeric, d float8, e hstestdom1); +insert into testhstore0 values (1, 'foo', 1.2, 3::float8); +insert into testhstore1 values (1, 'foo', 1.2, 3::float8); +select hstore(v) from testhstore1 v; + hstore +------------------------------------------------------ + "a"=>"1", "b"=>"foo", "c"=>"1.2", "d"=>"3", "e"=>"0" +(1 row) + +select hstore(null::testhstore0); + hstore +-------------------------------------------- + "a"=>NULL, "b"=>NULL, "c"=>NULL, "d"=>NULL +(1 row) + +select hstore(null::testhstore1); + hstore +------------------------------------------------------- + "a"=>NULL, "b"=>NULL, "c"=>NULL, "d"=>NULL, "e"=>NULL +(1 row) + +select pg_column_size(hstore(v)) + = pg_column_size('a=>1, b=>"foo", c=>"1.2", d=>"3", e=>"0"'::hstore) + from testhstore1 v; + ?column? +---------- + t +(1 row) + +select populate_record(v, ('c' => '3.45')) from testhstore1 v; + populate_record +------------------ + (1,foo,3.45,3,0) +(1 row) + +select populate_record(v, ('d' => '3.45')) from testhstore1 v; + populate_record +-------------------- + (1,foo,1.2,3.45,0) +(1 row) + +select populate_record(v, ('e' => '123')) from testhstore1 v; + populate_record +------------------- + (1,foo,1.2,3,123) +(1 row) + +select populate_record(v, ('e' => null)) from testhstore1 v; +ERROR: domain hstestdom1 does not allow null values +select populate_record(v, ('c' => null)) from testhstore1 v; + populate_record +----------------- + (1,foo,,3,0) +(1 row) + +select populate_record(v, ('b' => 'foo') || ('a' => '123')) from testhstore1 v; + populate_record +------------------- + (123,foo,1.2,3,0) +(1 row) + +select populate_record(v, ('b' => 'foo') || ('e' => null)) from testhstore0 v; + populate_record +----------------- + (1,foo,1.2,3) +(1 row) + +select populate_record(v, ('b' => 'foo') || ('e' => null)) from testhstore1 v; +ERROR: domain hstestdom1 does not allow null values +select populate_record(v, '') from testhstore0 v; + populate_record +----------------- + (1,foo,1.2,3) +(1 row) + +select populate_record(v, '') from testhstore1 v; + populate_record +----------------- + (1,foo,1.2,3,0) +(1 row) + +select populate_record(null::testhstore1, ('c' => '3.45') || ('a' => '123')); +ERROR: domain hstestdom1 does not allow null values +select populate_record(null::testhstore1, ('c' => '3.45') || ('e' => '123')); + populate_record +----------------- + (,,3.45,,123) +(1 row) + +select populate_record(null::testhstore0, ''); + populate_record +----------------- + (,,,) +(1 row) + +select populate_record(null::testhstore1, ''); +ERROR: domain hstestdom1 does not allow null values +select v #= ('c' => '3.45') from testhstore1 v; + ?column? +------------------ + (1,foo,3.45,3,0) +(1 row) + +select v #= ('d' => '3.45') from testhstore1 v; + ?column? +-------------------- + (1,foo,1.2,3.45,0) +(1 row) + +select v #= ('e' => '123') from testhstore1 v; + ?column? +------------------- + (1,foo,1.2,3,123) +(1 row) + +select v #= ('c' => null) from testhstore1 v; + ?column? +-------------- + (1,foo,,3,0) +(1 row) + +select v #= ('e' => null) from testhstore0 v; + ?column? +--------------- + (1,foo,1.2,3) +(1 row) + +select v #= ('e' => null) from testhstore1 v; +ERROR: domain hstestdom1 does not allow null values +select v #= (('b' => 'foo') || ('a' => '123')) from testhstore1 v; + ?column? +------------------- + (123,foo,1.2,3,0) +(1 row) + +select v #= (('b' => 'foo') || ('e' => '123')) from testhstore1 v; + ?column? +------------------- + (1,foo,1.2,3,123) +(1 row) + +select v #= hstore '' from testhstore0 v; + ?column? +--------------- + (1,foo,1.2,3) +(1 row) + +select v #= hstore '' from testhstore1 v; + ?column? +----------------- + (1,foo,1.2,3,0) +(1 row) + +select null::testhstore1 #= (('c' => '3.45') || ('a' => '123')); +ERROR: domain hstestdom1 does not allow null values +select null::testhstore1 #= (('c' => '3.45') || ('e' => '123')); + ?column? +--------------- + (,,3.45,,123) +(1 row) + +select null::testhstore0 #= hstore ''; + ?column? +---------- + (,,,) +(1 row) + +select null::testhstore1 #= hstore ''; +ERROR: domain hstestdom1 does not allow null values +select v #= h from testhstore1 v, (values (hstore 'a=>123',1),('b=>foo,c=>3.21',2),('a=>null',3),('e=>123',4),('f=>blah',5)) x(h,i) order by i; + ?column? +------------------- + (123,foo,1.2,3,0) + (1,foo,3.21,3,0) + (,foo,1.2,3,0) + (1,foo,1.2,3,123) + (1,foo,1.2,3,0) +(5 rows) + -- keys/values select akeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); akeys @@ -440,9 +1124,9 @@ select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); (1 row) select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL'); - avals ------------- - {g,1,l,""} + avals +-------------- + {g,1,l,NULL} (1 row) select avals('""=>1'); @@ -457,6 +1141,30 @@ select avals(''); {} (1 row) +select hstore_to_array('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore); + hstore_to_array +------------------------- + {b,g,aa,1,cq,l,fg,NULL} +(1 row) + +select %% 'aa=>1, cq=>l, b=>g, fg=>NULL'; + ?column? +------------------------- + {b,g,aa,1,cq,l,fg,NULL} +(1 row) + +select hstore_to_matrix('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore); + hstore_to_matrix +--------------------------------- + {{b,g},{aa,1},{cq,l},{fg,NULL}} +(1 row) + +select %# 'aa=>1, cq=>l, b=>g, fg=>NULL'; + ?column? +--------------------------------- + {{b,g},{aa,1},{cq,l},{fg,NULL}} +(1 row) + select * from skeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); skeys ------- @@ -583,6 +1291,18 @@ select count(*) from testhstore where h ? 'public'; 194 (1 row) +select count(*) from testhstore where h ?| ARRAY['public','disabled']; + count +------- + 337 +(1 row) + +select count(*) from testhstore where h ?& ARRAY['public','disabled']; + count +------- + 42 +(1 row) + create index hidx on testhstore using gist(h); set enable_seqscan=off; select count(*) from testhstore where h @> 'wait=>NULL'; @@ -609,6 +1329,18 @@ select count(*) from testhstore where h ? 'public'; 194 (1 row) +select count(*) from testhstore where h ?| ARRAY['public','disabled']; + count +------- + 337 +(1 row) + +select count(*) from testhstore where h ?& ARRAY['public','disabled']; + count +------- + 42 +(1 row) + drop index hidx; create index hidx on testhstore using gin (h); set enable_seqscan=off; @@ -636,6 +1368,18 @@ select count(*) from testhstore where h ? 'public'; 194 (1 row) +select count(*) from testhstore where h ?| ARRAY['public','disabled']; + count +------- + 337 +(1 row) + +select count(*) from testhstore where h ?& ARRAY['public','disabled']; + count +------- + 42 +(1 row) + select count(*) from (select (each(h)).key from testhstore) as wow ; count ------- @@ -669,3 +1413,48 @@ select key, count(*) from (select (each(h)).key from testhstore) as wow group by abstract | 161 (22 rows) +-- sort/hash +select count(distinct h) from testhstore; + count +------- + 885 +(1 row) + +set enable_hashagg = false; +select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2; + count +------- + 885 +(1 row) + +set enable_hashagg = true; +set enable_sort = false; +select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2; + count +------- + 885 +(1 row) + +select distinct * from (values (hstore '' || ''),('')) v(h); + h +--- + +(1 row) + +set enable_sort = true; +-- btree +drop index hidx; +create index hidx on testhstore using btree (h); +set enable_seqscan=off; +select count(*) from testhstore where h #># 'p=>1'; + count +------- + 125 +(1 row) + +select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t'; + count +------- + 1 +(1 row) + diff --git a/contrib/hstore/hstore.h b/contrib/hstore/hstore.h index e8ea58b567..495ac1afc9 100644 --- a/contrib/hstore/hstore.h +++ b/contrib/hstore/hstore.h @@ -1,59 +1,197 @@ /* - * $PostgreSQL: pgsql/contrib/hstore/hstore.h,v 1.8 2009/06/11 14:48:51 momjian Exp $ + * $PostgreSQL: pgsql/contrib/hstore/hstore.h,v 1.9 2009/09/30 19:50:22 tgl Exp $ */ #ifndef __HSTORE_H__ #define __HSTORE_H__ #include "fmgr.h" +#include "utils/array.h" +/* + * HEntry: there is one of these for each key _and_ value in an hstore + * + * the position offset points to the _end_ so that we can get the length + * by subtraction from the previous entry. the ISFIRST flag lets us tell + * whether there is a previous entry. + */ typedef struct { - uint16 keylen; - uint16 vallen; - uint32 - valisnull:1, - pos:31; + uint32 entry; } HEntry; -/* these are determined by the sizes of the keylen and vallen fields */ -/* in struct HEntry and struct Pairs */ -#define HSTORE_MAX_KEY_LEN 65535 -#define HSTORE_MAX_VALUE_LEN 65535 +#define HENTRY_ISFIRST 0x80000000 +#define HENTRY_ISNULL 0x40000000 +#define HENTRY_POSMASK 0x3FFFFFFF +/* note possible multiple evaluations, also access to prior array element */ +#define HSE_ISFIRST(he_) (((he_).entry & HENTRY_ISFIRST) != 0) +#define HSE_ISNULL(he_) (((he_).entry & HENTRY_ISNULL) != 0) +#define HSE_ENDPOS(he_) ((he_).entry & HENTRY_POSMASK) +#define HSE_OFF(he_) (HSE_ISFIRST(he_) ? 0 : HSE_ENDPOS((&(he_))[-1])) +#define HSE_LEN(he_) (HSE_ISFIRST(he_) \ + ? HSE_ENDPOS(he_) \ + : HSE_ENDPOS(he_) - HSE_ENDPOS((&(he_))[-1])) + +/* + * determined by the size of "endpos" (ie HENTRY_POSMASK), though this is a + * bit academic since currently varlenas (and hence both the input and the + * whole hstore) have the same limit + */ +#define HSTORE_MAX_KEY_LEN 0x3FFFFFFF +#define HSTORE_MAX_VALUE_LEN 0x3FFFFFFF typedef struct { int32 vl_len_; /* varlena header (do not touch directly!) */ - int4 size; - char data[1]; + uint32 size_; /* flags and number of items in hstore */ + /* array of HEntry follows */ } HStore; -#define HSHRDSIZE (VARHDRSZ + sizeof(int4)) -#define CALCDATASIZE(x, lenstr) ( (x) * sizeof(HEntry) + HSHRDSIZE + (lenstr) ) -#define ARRPTR(x) ( (HEntry*) ( (char*)(x) + HSHRDSIZE ) ) -#define STRPTR(x) ( (char*)(x) + HSHRDSIZE + ( sizeof(HEntry) * ((HStore*)x)->size ) ) +/* + * it's not possible to get more than 2^28 items into an hstore, + * so we reserve the top few bits of the size field. See hstore_compat.c + * for one reason why. Some bits are left for future use here. + */ +#define HS_FLAG_NEWVERSION 0x80000000 + +#define HS_COUNT(hsp_) ((hsp_)->size_ & 0x0FFFFFFF) +#define HS_SETCOUNT(hsp_,c_) ((hsp_)->size_ = (c_) | HS_FLAG_NEWVERSION) + + +#define HSHRDSIZE (sizeof(HStore)) +#define CALCDATASIZE(x, lenstr) ( (x) * 2 * sizeof(HEntry) + HSHRDSIZE + (lenstr) ) + +/* note multiple evaluations of x */ +#define ARRPTR(x) ( (HEntry*) ( (HStore*)(x) + 1 ) ) +#define STRPTR(x) ( (char*)(ARRPTR(x) + HS_COUNT((HStore*)(x)) * 2) ) + +/* note multiple/non evaluations */ +#define HS_KEY(arr_,str_,i_) ((str_) + HSE_OFF((arr_)[2*(i_)])) +#define HS_VAL(arr_,str_,i_) ((str_) + HSE_OFF((arr_)[2*(i_)+1])) +#define HS_KEYLEN(arr_,i_) (HSE_LEN((arr_)[2*(i_)])) +#define HS_VALLEN(arr_,i_) (HSE_LEN((arr_)[2*(i_)+1])) +#define HS_VALISNULL(arr_,i_) (HSE_ISNULL((arr_)[2*(i_)+1])) + +/* + * currently, these following macros are the _only_ places that rely + * on internal knowledge of HEntry. Everything else should be using + * the above macros. Exception: the in-place upgrade in hstore_compat.c + * messes with entries directly. + */ + +/* + * copy one key/value pair (which must be contiguous starting at + * sptr_) into an under-construction hstore; dent_ is an HEntry*, + * dbuf_ is the destination's string buffer, dptr_ is the current + * position in the destination. lots of modification and multiple + * evaluation here. + */ +#define HS_COPYITEM(dent_,dbuf_,dptr_,sptr_,klen_,vlen_,vnull_) \ + do { \ + memcpy((dptr_), (sptr_), (klen_)+(vlen_)); \ + (dptr_) += (klen_)+(vlen_); \ + (dent_)++->entry = ((dptr_) - (dbuf_) - (vlen_)) & HENTRY_POSMASK; \ + (dent_)++->entry = ((((dptr_) - (dbuf_)) & HENTRY_POSMASK) \ + | ((vnull_) ? HENTRY_ISNULL : 0)); \ + } while(0) + +/* + * add one key/item pair, from a Pairs structure, into an + * under-construction hstore + */ +#define HS_ADDITEM(dent_,dbuf_,dptr_,pair_) \ + do { \ + memcpy((dptr_), (pair_).key, (pair_).keylen); \ + (dptr_) += (pair_).keylen; \ + (dent_)++->entry = ((dptr_) - (dbuf_)) & HENTRY_POSMASK; \ + if ((pair_).isnull) \ + (dent_)++->entry = ((((dptr_) - (dbuf_)) & HENTRY_POSMASK) \ + | HENTRY_ISNULL); \ + else \ + { \ + memcpy((dptr_), (pair_).val, (pair_).vallen); \ + (dptr_) += (pair_).vallen; \ + (dent_)++->entry = ((dptr_) - (dbuf_)) & HENTRY_POSMASK; \ + } \ + } while (0) + +/* finalize a newly-constructed hstore */ +#define HS_FINALIZE(hsp_,count_,buf_,ptr_) \ + do { \ + int buflen = (ptr_) - (buf_); \ + if ((count_)) \ + ARRPTR(hsp_)[0].entry |= HENTRY_ISFIRST; \ + if ((count_) != HS_COUNT((hsp_))) \ + { \ + HS_SETCOUNT((hsp_),(count_)); \ + memmove(STRPTR(hsp_), (buf_), buflen); \ + } \ + SET_VARSIZE((hsp_), CALCDATASIZE((count_), buflen)); \ + } while (0) + +/* ensure the varlena size of an existing hstore is correct */ +#define HS_FIXSIZE(hsp_,count_) \ + do { \ + int bl = (count_) ? HSE_ENDPOS(ARRPTR(hsp_)[2*(count_)-1]) : 0; \ + SET_VARSIZE((hsp_), CALCDATASIZE((count_),bl)); \ + } while (0) + +/* DatumGetHStoreP includes support for reading old-format hstore values */ +extern HStore *hstoreUpgrade(Datum orig); +#define DatumGetHStoreP(d) hstoreUpgrade(d) -#define PG_GETARG_HS(x) ((HStore*)PG_DETOAST_DATUM(PG_GETARG_DATUM(x))) +#define PG_GETARG_HS(x) DatumGetHStoreP(PG_GETARG_DATUM(x)) + +/* + * Pairs is a "decompressed" representation of one key/value pair. + * The two strings are not necessarily null-terminated. + */ typedef struct { char *key; char *val; - uint16 keylen; - uint16 vallen; - bool isnull; - bool needfree; + size_t keylen; + size_t vallen; + bool isnull; /* value is null? */ + bool needfree; /* need to pfree the value? */ } Pairs; -int comparePairs(const void *a, const void *b); -int uniquePairs(Pairs *a, int4 l, int4 *buflen); +extern int hstoreUniquePairs(Pairs *a, int4 l, int4 *buflen); +extern HStore *hstorePairs(Pairs *pairs, int4 pcount, int4 buflen); + +extern size_t hstoreCheckKeyLen(size_t len); +extern size_t hstoreCheckValLen(size_t len); -size_t hstoreCheckKeyLen(size_t len); -size_t hstoreCheckValLen(size_t len); +extern int hstoreFindKey(HStore *hs, int *lowbound, char *key, int keylen); +extern Pairs *hstoreArrayToPairs(ArrayType *a, int *npairs); #define HStoreContainsStrategyNumber 7 #define HStoreExistsStrategyNumber 9 +#define HStoreExistsAnyStrategyNumber 10 +#define HStoreExistsAllStrategyNumber 11 +#define HStoreOldContainsStrategyNumber 13 /* backwards compatibility */ + +/* + * defining HSTORE_POLLUTE_NAMESPACE=0 will prevent use of old function names; + * for now, we default to on for the benefit of people restoring old dumps + */ +#ifndef HSTORE_POLLUTE_NAMESPACE +#define HSTORE_POLLUTE_NAMESPACE 1 +#endif + +#if HSTORE_POLLUTE_NAMESPACE +#define HSTORE_POLLUTE(newname_,oldname_) \ + PG_FUNCTION_INFO_V1(oldname_); \ + Datum oldname_(PG_FUNCTION_ARGS); \ + Datum newname_(PG_FUNCTION_ARGS); \ + Datum oldname_(PG_FUNCTION_ARGS) { return newname_(fcinfo); } \ + extern int no_such_variable +#else +#define HSTORE_POLLUTE(newname_,oldname_) \ + extern int no_such_variable +#endif #endif /* __HSTORE_H__ */ diff --git a/contrib/hstore/hstore.sql.in b/contrib/hstore/hstore.sql.in index 29a78ed052..75c4367ccc 100644 --- a/contrib/hstore/hstore.sql.in +++ b/contrib/hstore/hstore.sql.in @@ -1,4 +1,4 @@ -/* $PostgreSQL: pgsql/contrib/hstore/hstore.sql.in,v 1.11 2009/06/11 18:30:03 tgl Exp $ */ +/* $PostgreSQL: pgsql/contrib/hstore/hstore.sql.in,v 1.12 2009/09/30 19:50:22 tgl Exp $ */ -- Adjust this setting to control where the objects get created. SET search_path = public; @@ -8,23 +8,40 @@ CREATE TYPE hstore; CREATE OR REPLACE FUNCTION hstore_in(cstring) RETURNS hstore AS 'MODULE_PATHNAME' -LANGUAGE C STRICT; +LANGUAGE C STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION hstore_out(hstore) RETURNS cstring AS 'MODULE_PATHNAME' -LANGUAGE C STRICT; +LANGUAGE C STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION hstore_recv(internal) +RETURNS hstore +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION hstore_send(hstore) +RETURNS bytea +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; CREATE TYPE hstore ( INTERNALLENGTH = -1, INPUT = hstore_in, OUTPUT = hstore_out, + RECEIVE = hstore_recv, + SEND = hstore_send, STORAGE = extended ); +CREATE OR REPLACE FUNCTION hstore_version_diag(hstore) +RETURNS integer +AS 'MODULE_PATHNAME','hstore_version_diag' +LANGUAGE C STRICT IMMUTABLE; + CREATE OR REPLACE FUNCTION fetchval(hstore,text) RETURNS text -AS 'MODULE_PATHNAME' +AS 'MODULE_PATHNAME','hstore_fetchval' LANGUAGE C STRICT IMMUTABLE; CREATE OPERATOR -> ( @@ -33,14 +50,36 @@ CREATE OPERATOR -> ( PROCEDURE = fetchval ); +CREATE OR REPLACE FUNCTION slice_array(hstore,text[]) +RETURNS text[] +AS 'MODULE_PATHNAME','hstore_slice_to_array' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR -> ( + LEFTARG = hstore, + RIGHTARG = text[], + PROCEDURE = slice_array +); + +CREATE OR REPLACE FUNCTION slice_hstore(hstore,text[]) +RETURNS hstore +AS 'MODULE_PATHNAME','hstore_slice_to_hstore' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR => ( + LEFTARG = hstore, + RIGHTARG = text[], + PROCEDURE = slice_hstore +); + CREATE OR REPLACE FUNCTION isexists(hstore,text) RETURNS bool -AS 'MODULE_PATHNAME','exists' +AS 'MODULE_PATHNAME','hstore_exists' LANGUAGE C STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION exist(hstore,text) RETURNS bool -AS 'MODULE_PATHNAME','exists' +AS 'MODULE_PATHNAME','hstore_exists' LANGUAGE C STRICT IMMUTABLE; CREATE OPERATOR ? ( @@ -51,24 +90,78 @@ CREATE OPERATOR ? ( JOIN = contjoinsel ); +CREATE OR REPLACE FUNCTION exists_any(hstore,text[]) +RETURNS bool +AS 'MODULE_PATHNAME','hstore_exists_any' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR ?| ( + LEFTARG = hstore, + RIGHTARG = text[], + PROCEDURE = exists_any, + RESTRICT = contsel, + JOIN = contjoinsel +); + +CREATE OR REPLACE FUNCTION exists_all(hstore,text[]) +RETURNS bool +AS 'MODULE_PATHNAME','hstore_exists_all' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR ?& ( + LEFTARG = hstore, + RIGHTARG = text[], + PROCEDURE = exists_all, + RESTRICT = contsel, + JOIN = contjoinsel +); + CREATE OR REPLACE FUNCTION isdefined(hstore,text) RETURNS bool -AS 'MODULE_PATHNAME','defined' +AS 'MODULE_PATHNAME','hstore_defined' LANGUAGE C STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION defined(hstore,text) RETURNS bool -AS 'MODULE_PATHNAME','defined' +AS 'MODULE_PATHNAME','hstore_defined' LANGUAGE C STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION delete(hstore,text) RETURNS hstore -AS 'MODULE_PATHNAME','delete' +AS 'MODULE_PATHNAME','hstore_delete' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION delete(hstore,text[]) +RETURNS hstore +AS 'MODULE_PATHNAME','hstore_delete_array' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION delete(hstore,hstore) +RETURNS hstore +AS 'MODULE_PATHNAME','hstore_delete_hstore' LANGUAGE C STRICT IMMUTABLE; +CREATE OPERATOR - ( + LEFTARG = hstore, + RIGHTARG = text, + PROCEDURE = delete +); + +CREATE OPERATOR - ( + LEFTARG = hstore, + RIGHTARG = text[], + PROCEDURE = delete +); + +CREATE OPERATOR - ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = delete +); + CREATE OR REPLACE FUNCTION hs_concat(hstore,hstore) RETURNS hstore -AS 'MODULE_PATHNAME' +AS 'MODULE_PATHNAME','hstore_concat' LANGUAGE C STRICT IMMUTABLE; CREATE OPERATOR || ( @@ -79,12 +172,12 @@ CREATE OPERATOR || ( CREATE OR REPLACE FUNCTION hs_contains(hstore,hstore) RETURNS bool -AS 'MODULE_PATHNAME' +AS 'MODULE_PATHNAME','hstore_contains' LANGUAGE C STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION hs_contained(hstore,hstore) RETURNS bool -AS 'MODULE_PATHNAME' +AS 'MODULE_PATHNAME','hstore_contained' LANGUAGE C STRICT IMMUTABLE; CREATE OPERATOR @> ( @@ -126,57 +219,237 @@ CREATE OPERATOR ~ ( CREATE OR REPLACE FUNCTION tconvert(text,text) RETURNS hstore -AS 'MODULE_PATHNAME' -LANGUAGE C IMMUTABLE; -- not STRICT +AS 'MODULE_PATHNAME','hstore_from_text' +LANGUAGE C IMMUTABLE; -- not STRICT; needs to allow (key,NULL) + +CREATE OR REPLACE FUNCTION hstore(text,text) +RETURNS hstore +AS 'MODULE_PATHNAME','hstore_from_text' +LANGUAGE C IMMUTABLE; -- not STRICT; needs to allow (key,NULL) CREATE OPERATOR => ( LEFTARG = text, RIGHTARG = text, - PROCEDURE = tconvert + PROCEDURE = hstore +); + +CREATE OR REPLACE FUNCTION hstore(text[],text[]) +RETURNS hstore +AS 'MODULE_PATHNAME', 'hstore_from_arrays' +LANGUAGE C IMMUTABLE; -- not STRICT; allows (keys,null) + +CREATE OPERATOR => ( + LEFTARG = text[], + RIGHTARG = text[], + PROCEDURE = hstore +); + +CREATE FUNCTION hstore(text[]) +RETURNS hstore +AS 'MODULE_PATHNAME', 'hstore_from_array' +LANGUAGE C IMMUTABLE STRICT; + +CREATE CAST (text[] AS hstore) + WITH FUNCTION hstore(text[]); + +CREATE OR REPLACE FUNCTION hstore(record) +RETURNS hstore +AS 'MODULE_PATHNAME', 'hstore_from_record' +LANGUAGE C IMMUTABLE; -- not STRICT; allows (null::recordtype) + +CREATE OR REPLACE FUNCTION hstore_to_array(hstore) +RETURNS text[] +AS 'MODULE_PATHNAME','hstore_to_array' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR %% ( + RIGHTARG = hstore, + PROCEDURE = hstore_to_array +); + +CREATE OR REPLACE FUNCTION hstore_to_matrix(hstore) +RETURNS text[] +AS 'MODULE_PATHNAME','hstore_to_matrix' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR %# ( + RIGHTARG = hstore, + PROCEDURE = hstore_to_matrix ); CREATE OR REPLACE FUNCTION akeys(hstore) -RETURNS _text -AS 'MODULE_PATHNAME' +RETURNS text[] +AS 'MODULE_PATHNAME','hstore_akeys' LANGUAGE C STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION avals(hstore) -RETURNS _text -AS 'MODULE_PATHNAME' +RETURNS text[] +AS 'MODULE_PATHNAME','hstore_avals' LANGUAGE C STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION skeys(hstore) RETURNS setof text -AS 'MODULE_PATHNAME' +AS 'MODULE_PATHNAME','hstore_skeys' LANGUAGE C STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION svals(hstore) RETURNS setof text -AS 'MODULE_PATHNAME' +AS 'MODULE_PATHNAME','hstore_svals' LANGUAGE C STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION each(IN hs hstore, OUT key text, OUT value text) RETURNS SETOF record -AS 'MODULE_PATHNAME' +AS 'MODULE_PATHNAME','hstore_each' LANGUAGE C STRICT IMMUTABLE; +CREATE OR REPLACE FUNCTION populate_record(anyelement,hstore) +RETURNS anyelement +AS 'MODULE_PATHNAME', 'hstore_populate_record' +LANGUAGE C IMMUTABLE; -- not STRICT; allows (null::rectype,hstore) +CREATE OPERATOR #= ( + LEFTARG = anyelement, + RIGHTARG = hstore, + PROCEDURE = populate_record +); + +-- btree support --- define the GiST support methods +CREATE OR REPLACE FUNCTION hstore_eq(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_eq' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION hstore_ne(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_ne' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION hstore_gt(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_gt' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION hstore_ge(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_ge' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION hstore_lt(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_lt' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION hstore_le(hstore,hstore) +RETURNS boolean +AS 'MODULE_PATHNAME','hstore_le' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION hstore_cmp(hstore,hstore) +RETURNS integer +AS 'MODULE_PATHNAME','hstore_cmp' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR = ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_eq, + COMMUTATOR = =, + NEGATOR = <>, + RESTRICT = eqsel, + JOIN = eqjoinsel, + MERGES, + HASHES +); +CREATE OPERATOR <> ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_ne, + COMMUTATOR = <>, + NEGATOR = =, + RESTRICT = neqsel, + JOIN = neqjoinsel +); + +-- the comparison operators have funky names (and are undocumented) +-- in an attempt to discourage anyone from actually using them. they +-- only exist to support the btree opclass + +CREATE OPERATOR #<# ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_lt, + COMMUTATOR = #>#, + NEGATOR = #>=#, + RESTRICT = scalarltsel, + JOIN = scalarltjoinsel +); +CREATE OPERATOR #<=# ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_le, + COMMUTATOR = #>=#, + NEGATOR = #>#, + RESTRICT = scalarltsel, + JOIN = scalarltjoinsel +); +CREATE OPERATOR #># ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_gt, + COMMUTATOR = #<#, + NEGATOR = #<=#, + RESTRICT = scalargtsel, + JOIN = scalargtjoinsel +); +CREATE OPERATOR #>=# ( + LEFTARG = hstore, + RIGHTARG = hstore, + PROCEDURE = hstore_ge, + COMMUTATOR = #<=#, + NEGATOR = #<#, + RESTRICT = scalargtsel, + JOIN = scalargtjoinsel +); + +CREATE OPERATOR CLASS btree_hstore_ops +DEFAULT FOR TYPE hstore USING btree +AS + OPERATOR 1 #<# , + OPERATOR 2 #<=# , + OPERATOR 3 = , + OPERATOR 4 #>=# , + OPERATOR 5 #># , + FUNCTION 1 hstore_cmp(hstore,hstore); + +-- hash support + +CREATE OR REPLACE FUNCTION hstore_hash(hstore) +RETURNS integer +AS 'MODULE_PATHNAME','hstore_hash' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS hash_hstore_ops +DEFAULT FOR TYPE hstore USING hash +AS + OPERATOR 1 = , + FUNCTION 1 hstore_hash(hstore); + +-- GiST support CREATE TYPE ghstore; CREATE OR REPLACE FUNCTION ghstore_in(cstring) RETURNS ghstore AS 'MODULE_PATHNAME' -LANGUAGE C STRICT; +LANGUAGE C STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION ghstore_out(ghstore) RETURNS cstring AS 'MODULE_PATHNAME' -LANGUAGE C STRICT; +LANGUAGE C STRICT IMMUTABLE; CREATE TYPE ghstore ( INTERNALLENGTH = -1, @@ -219,12 +492,13 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; --- register the opclass for indexing (not as default) CREATE OPERATOR CLASS gist_hstore_ops DEFAULT FOR TYPE hstore USING gist AS - OPERATOR 7 @> , - OPERATOR 9 ?(hstore,text) , + OPERATOR 7 @> , + OPERATOR 9 ?(hstore,text) , + OPERATOR 10 ?|(hstore,text[]) , + OPERATOR 11 ?&(hstore,text[]) , --OPERATOR 8 <@ , OPERATOR 13 @ , --OPERATOR 14 ~ , @@ -237,7 +511,7 @@ AS FUNCTION 7 ghstore_same (internal, internal, internal), STORAGE ghstore; --- define the GIN support methods +-- GIN support CREATE OR REPLACE FUNCTION gin_extract_hstore(internal, internal) RETURNS internal @@ -257,10 +531,12 @@ LANGUAGE C IMMUTABLE STRICT; CREATE OPERATOR CLASS gin_hstore_ops DEFAULT FOR TYPE hstore USING gin AS - OPERATOR 7 @> , + OPERATOR 7 @>, OPERATOR 9 ?(hstore,text), + OPERATOR 10 ?|(hstore,text[]), + OPERATOR 11 ?&(hstore,text[]), FUNCTION 1 bttextcmp(text,text), FUNCTION 2 gin_extract_hstore(internal, internal), FUNCTION 3 gin_extract_hstore_query(internal, internal, int2, internal, internal), FUNCTION 4 gin_consistent_hstore(internal, int2, internal, int4, internal, internal), -STORAGE text; + STORAGE text; diff --git a/contrib/hstore/hstore_compat.c b/contrib/hstore/hstore_compat.c new file mode 100644 index 0000000000..e2c2b55c10 --- /dev/null +++ b/contrib/hstore/hstore_compat.c @@ -0,0 +1,376 @@ +/* + * $PostgreSQL: pgsql/contrib/hstore/hstore_compat.c,v 1.1 2009/09/30 19:50:22 tgl Exp $ + * + * Notes on old/new hstore format disambiguation. + * + * There are three formats to consider: + * 1) old contrib/hstore (referred to as hstore-old) + * 2) prerelease pgfoundry hstore + * 3) new contrib/hstore + * + * (2) and (3) are identical except for the HS_FLAG_NEWVERSION + * bit, which is set in (3) but not (2). + * + * Values that are already in format (3), or which are + * unambiguously in format (2), are handled by the first + * "return immediately" test in hstoreUpgrade(). + * + * To stress a point: we ONLY get here with possibly-ambiguous + * values if we're doing some sort of in-place migration from an + * old prerelease pgfoundry hstore-new; and we explicitly don't + * support that without fixing up any potentially padded values + * first. Most of the code here is serious overkill, but the + * performance penalty isn't serious (especially compared to the + * palloc() that we have to do anyway) and the belt-and-braces + * validity checks provide some reassurance. (If for some reason + * we get a value that would have worked on the old code, but + * which would be botched by the conversion code, the validity + * checks will fail it first so we get an error rather than bad + * data.) + * + * Note also that empty hstores are the same in (2) and (3), so + * there are some special-case paths for them. + * + * We tell the difference between formats (2) and (3) as follows (but + * note that there are some edge cases where we can't tell; see + * comments in hstoreUpgrade): + * + * First, since there must be at least one entry, we look at + * how the bits line up. The new format looks like: + * + * 10kkkkkkkkkkkkkkkkkkkkkkkkkkkkkk (k..k = keylen) + * 0nvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv (v..v = keylen+vallen) + * + * The old format looks like one of these, depending on endianness + * and bitfield layout: (k..k = keylen, v..v = vallen, p..p = pos, + * n = isnull) + * + * kkkkkkkkkkkkkkkkvvvvvvvvvvvvvvvv + * nppppppppppppppppppppppppppppppp + * + * kkkkkkkkkkkkkkkkvvvvvvvvvvvvvvvv + * pppppppppppppppppppppppppppppppn + * + * vvvvvvvvvvvvvvvvkkkkkkkkkkkkkkkk + * nppppppppppppppppppppppppppppppp + * + * vvvvvvvvvvvvvvvvkkkkkkkkkkkkkkkk + * pppppppppppppppppppppppppppppppn (usual i386 format) + * + * If the entry is in old format, for the first entry "pos" must be 0. + * We can obviously see that either keylen or vallen must be >32768 + * for there to be any ambiguity (which is why lengths less than that + * are fasttracked in hstore.h) Since "pos"==0, the "v" field in the + * new-format interpretation can only be 0 or 1, which constrains all + * but three bits of the old-format's k and v fields. But in addition + * to all of this, the data length implied by the keylen and vallen + * must fit in the varlena size. So the only ambiguous edge case for + * hstores with only one entry occurs between a new-format entry with + * an excess (~32k) of padding, and an old-format entry. But we know + * which format to use in that case based on how we were compiled, so + * no actual data corruption can occur. + * + * If there is more than one entry, the requirement that keys do not + * decrease in length, and that positions increase contiguously, and + * that the end of the data not be beyond the end of the varlena + * itself, disambiguates in almost all other cases. There is a small + * set of ambiguous cases which could occur if the old-format value + * has a large excess of padding and just the right pattern of key + * sizes, but these are also handled based on how we were compiled. + * + * The otherwise undocumented function hstore_version_diag is provided + * for testing purposes. + */ +#include "postgres.h" + +#include "funcapi.h" + +#include "hstore.h" + +/* + * This is the structure used for entries in the old contrib/hstore + * implementation. Notice that this is the same size as the new entry + * (two 32-bit words per key/value pair) and that the header is the + * same, so the old and new versions of ARRPTR, STRPTR, CALCDATASIZE + * etc. are compatible. + * + * If the above statement isn't true on some bizarre platform, we're + * a bit hosed (see Assert in hstoreValidOldFormat). + */ +typedef struct +{ + uint16 keylen; + uint16 vallen; + uint32 + valisnull:1, + pos:31; +} HOldEntry; + +static int hstoreValidNewFormat(HStore *hs); +static int hstoreValidOldFormat(HStore *hs); + + +/* + * Validity test for a new-format hstore. + * 0 = not valid + * 1 = valid but with "slop" in the length + * 2 = exactly valid + */ +static int +hstoreValidNewFormat(HStore *hs) +{ + int count = HS_COUNT(hs); + HEntry *entries = ARRPTR(hs); + int buflen = (count) ? HSE_ENDPOS(entries[2*(count)-1]) : 0; + int vsize = CALCDATASIZE(count,buflen); + int i; + + if (hs->size_ & HS_FLAG_NEWVERSION) + return 2; + + if (count == 0) + return 2; + + if (!HSE_ISFIRST(entries[0])) + return 0; + + if (vsize > VARSIZE(hs)) + return 0; + + /* entry position must be nondecreasing */ + + for (i = 1; i < 2*count; ++i) + { + if (HSE_ISFIRST(entries[i]) + || (HSE_ENDPOS(entries[i]) < HSE_ENDPOS(entries[i-1]))) + return 0; + } + + /* key length must be nondecreasing and keys must not be null */ + + for (i = 1; i < count; ++i) + { + if (HS_KEYLEN(entries,i) < HS_KEYLEN(entries,i-1)) + return 0; + if (HSE_ISNULL(entries[2*i])) + return 0; + } + + if (vsize != VARSIZE(hs)) + return 1; + + return 2; +} + +/* + * Validity test for an old-format hstore. + * 0 = not valid + * 1 = valid but with "slop" in the length + * 2 = exactly valid + */ +static int +hstoreValidOldFormat(HStore *hs) +{ + int count = hs->size_; + HOldEntry *entries = (HOldEntry *) ARRPTR(hs); + int vsize; + int lastpos = 0; + int i; + + if (hs->size_ & HS_FLAG_NEWVERSION) + return 0; + + Assert(sizeof(HOldEntry) == sizeof(HEntry)); + + if (count == 0) + return 2; + + if (count > 0xFFFFFFF) + return 0; + + if (CALCDATASIZE(count,0) > VARSIZE(hs)) + return 0; + + if (entries[0].pos != 0) + return 0; + + /* key length must be nondecreasing */ + + for (i = 1; i < count; ++i) + { + if (entries[i].keylen < entries[i-1].keylen) + return 0; + } + + /* + * entry position must be strictly increasing, except for the + * first entry (which can be ""=>"" and thus zero-length); and + * all entries must be properly contiguous + */ + + for (i = 0; i < count; ++i) + { + if (entries[i].pos != lastpos) + return 0; + lastpos += (entries[i].keylen + + ((entries[i].valisnull) ? 0 : entries[i].vallen)); + } + + vsize = CALCDATASIZE(count,lastpos); + + if (vsize > VARSIZE(hs)) + return 0; + + if (vsize != VARSIZE(hs)) + return 1; + + return 2; +} + + +/* + * hstoreUpgrade: PG_DETOAST_DATUM plus support for conversion of old hstores + */ +HStore * +hstoreUpgrade(Datum orig) +{ + HStore *hs = (HStore *) PG_DETOAST_DATUM(orig); + int valid_new; + int valid_old; + bool writable; + + /* Return immediately if no conversion needed */ + if ((hs->size_ & HS_FLAG_NEWVERSION) || + hs->size_ == 0 || + (VARSIZE(hs) < 32768 && HSE_ISFIRST((ARRPTR(hs)[0])))) + return hs; + + valid_new = hstoreValidNewFormat(hs); + valid_old = hstoreValidOldFormat(hs); + /* Do we have a writable copy? */ + writable = ((void *) hs != (void *) DatumGetPointer(orig)); + + if (!valid_old || hs->size_ == 0) + { + if (valid_new) + { + /* + * force the "new version" flag and the correct varlena + * length, but only if we have a writable copy already + * (which we almost always will, since short new-format + * values won't come through here) + */ + if (writable) + { + HS_SETCOUNT(hs,HS_COUNT(hs)); + HS_FIXSIZE(hs,HS_COUNT(hs)); + } + return hs; + } + else + { + elog(ERROR,"invalid hstore value found"); + } + } + + /* + * this is the tricky edge case. It is only possible in some + * quite extreme cases (the hstore must have had a lot + * of wasted padding space at the end). + * But the only way a "new" hstore value could get here is if + * we're upgrading in place from a pre-release version of + * hstore-new (NOT contrib/hstore), so we work off the following + * assumptions: + * 1. If you're moving from old contrib/hstore to hstore-new, + * you're required to fix up any potential conflicts first, + * e.g. by running ALTER TABLE ... USING col::text::hstore; + * on all hstore columns before upgrading. + * 2. If you're moving from old contrib/hstore to new + * contrib/hstore, then "new" values are impossible here + * 3. If you're moving from pre-release hstore-new to hstore-new, + * then "old" values are impossible here + * 4. If you're moving from pre-release hstore-new to new + * contrib/hstore, you're not doing so as an in-place upgrade, + * so there is no issue + * So the upshot of all this is that we can treat all the edge + * cases as "new" if we're being built as hstore-new, and "old" + * if we're being built as contrib/hstore. + * + * XXX the WARNING can probably be downgraded to DEBUG1 once this + * has been beta-tested. But for now, it would be very useful to + * know if anyone can actually reach this case in a non-contrived + * setting. + */ + + if (valid_new) + { +#if HSTORE_IS_HSTORE_NEW + elog(WARNING,"ambiguous hstore value resolved as hstore-new"); + + /* + * force the "new version" flag and the correct varlena + * length, but only if we have a writable copy already + * (which we almost always will, since short new-format + * values won't come through here) + */ + if (writable) + { + HS_SETCOUNT(hs,HS_COUNT(hs)); + HS_FIXSIZE(hs,HS_COUNT(hs)); + } + return hs; +#else + elog(WARNING,"ambiguous hstore value resolved as hstore-old"); +#endif + } + + /* + * must have an old-style value. Overwrite it in place as a new-style + * one, making sure we have a writable copy first. + */ + + if (!writable) + hs = (HStore *) PG_DETOAST_DATUM_COPY(orig); + + { + int count = hs->size_; + HEntry *new_entries = ARRPTR(hs); + HOldEntry *old_entries = (HOldEntry *) ARRPTR(hs); + int i; + + for (i = 0; i < count; ++i) + { + uint32 pos = old_entries[i].pos; + uint32 keylen = old_entries[i].keylen; + uint32 vallen = old_entries[i].vallen; + bool isnull = old_entries[i].valisnull; + + if (isnull) + vallen = 0; + + new_entries[2*i].entry = (pos + keylen) & HENTRY_POSMASK; + new_entries[2*i+1].entry = (((pos + keylen + vallen) & HENTRY_POSMASK) + | ((isnull) ? HENTRY_ISNULL : 0)); + } + + if (count) + new_entries[0].entry |= HENTRY_ISFIRST; + HS_SETCOUNT(hs,count); + HS_FIXSIZE(hs,count); + } + + return hs; +} + + +PG_FUNCTION_INFO_V1(hstore_version_diag); +Datum hstore_version_diag(PG_FUNCTION_ARGS); +Datum +hstore_version_diag(PG_FUNCTION_ARGS) +{ + HStore *hs = (HStore *) PG_DETOAST_DATUM(PG_GETARG_DATUM(0)); + int valid_new = hstoreValidNewFormat(hs); + int valid_old = hstoreValidOldFormat(hs); + + PG_RETURN_INT32(valid_old*10 + valid_new); +} diff --git a/contrib/hstore/hstore_gin.c b/contrib/hstore/hstore_gin.c index 9c9a83d128..3bd9d718bb 100644 --- a/contrib/hstore/hstore_gin.c +++ b/contrib/hstore/hstore_gin.c @@ -1,9 +1,10 @@ /* - * $PostgreSQL: pgsql/contrib/hstore/hstore_gin.c,v 1.6 2009/06/11 14:48:51 momjian Exp $ + * $PostgreSQL: pgsql/contrib/hstore/hstore_gin.c,v 1.7 2009/09/30 19:50:22 tgl Exp $ */ #include "postgres.h" #include "access/gin.h" +#include "catalog/pg_type.h" #include "hstore.h" @@ -35,43 +36,36 @@ gin_extract_hstore(PG_FUNCTION_ARGS) HStore *hs = PG_GETARG_HS(0); int32 *nentries = (int32 *) PG_GETARG_POINTER(1); Datum *entries = NULL; + HEntry *hsent = ARRPTR(hs); + char *ptr = STRPTR(hs); + int count = HS_COUNT(hs); + int i; - *nentries = 2 * hs->size; + *nentries = 2 * count; + if (count) + entries = (Datum *) palloc(sizeof(Datum) * 2 * count); - if (hs->size > 0) + for (i = 0; i < count; ++i) { - HEntry *ptr = ARRPTR(hs); - char *words = STRPTR(hs); - int i = 0; + text *item; - entries = (Datum *) palloc(sizeof(Datum) * 2 * hs->size); + item = makeitem(HS_KEY(hsent,ptr,i), HS_KEYLEN(hsent,i)); + *VARDATA(item) = KEYFLAG; + entries[2*i] = PointerGetDatum(item); - while (ptr - ARRPTR(hs) < hs->size) + if (HS_VALISNULL(hsent,i)) { - text *item; - - item = makeitem(words + ptr->pos, ptr->keylen); - *VARDATA(item) = KEYFLAG; - entries[i++] = PointerGetDatum(item); - - if (ptr->valisnull) - { - item = makeitem(NULL, 0); - *VARDATA(item) = NULLFLAG; - - } - else - { - item = makeitem(words + ptr->pos + ptr->keylen, ptr->vallen); - *VARDATA(item) = VALFLAG; - } - entries[i++] = PointerGetDatum(item); - - ptr++; + item = makeitem(NULL, 0); + *VARDATA(item) = NULLFLAG; } + else + { + item = makeitem(HS_VAL(hsent,ptr,i), HS_VALLEN(hsent,i)); + *VARDATA(item) = VALFLAG; + } + entries[2*i+1] = PointerGetDatum(item); } - PG_FREE_IF_COPY(hs, 0); PG_RETURN_POINTER(entries); } @@ -85,8 +79,7 @@ gin_extract_hstore_query(PG_FUNCTION_ARGS) if (strategy == HStoreContainsStrategyNumber) { - PG_RETURN_DATUM(DirectFunctionCall2( - gin_extract_hstore, + PG_RETURN_DATUM(DirectFunctionCall2(gin_extract_hstore, PG_GETARG_DATUM(0), PG_GETARG_DATUM(1) )); @@ -94,19 +87,50 @@ gin_extract_hstore_query(PG_FUNCTION_ARGS) else if (strategy == HStoreExistsStrategyNumber) { text *item, - *q = PG_GETARG_TEXT_P(0); + *query = PG_GETARG_TEXT_PP(0); int32 *nentries = (int32 *) PG_GETARG_POINTER(1); Datum *entries = NULL; *nentries = 1; entries = (Datum *) palloc(sizeof(Datum)); - item = makeitem(VARDATA(q), VARSIZE(q) - VARHDRSZ); + item = makeitem(VARDATA_ANY(query), VARSIZE_ANY_EXHDR(query)); *VARDATA(item) = KEYFLAG; entries[0] = PointerGetDatum(item); PG_RETURN_POINTER(entries); } + else if (strategy == HStoreExistsAnyStrategyNumber || + strategy == HStoreExistsAllStrategyNumber) + { + ArrayType *query = PG_GETARG_ARRAYTYPE_P(0); + Datum *key_datums; + bool *key_nulls; + int key_count; + int i,j; + int32 *nentries = (int32 *) PG_GETARG_POINTER(1); + Datum *entries = NULL; + text *item; + + deconstruct_array(query, + TEXTOID, -1, false, 'i', + &key_datums, &key_nulls, &key_count); + + entries = (Datum *) palloc(sizeof(Datum) * key_count); + + for (i = 0, j = 0; i < key_count; ++i) + { + if (key_nulls[i]) + continue; + item = makeitem(VARDATA(key_datums[i]), VARSIZE(key_datums[i]) - VARHDRSZ); + *VARDATA(item) = KEYFLAG; + entries[j++] = PointerGetDatum(item); + } + + *nentries = j ? j : -1; + + PG_RETURN_POINTER(entries); + } else elog(ERROR, "Unsupported strategy number: %d", strategy); @@ -121,32 +145,45 @@ gin_consistent_hstore(PG_FUNCTION_ARGS) { bool *check = (bool *) PG_GETARG_POINTER(0); StrategyNumber strategy = PG_GETARG_UINT16(1); - HStore *query = PG_GETARG_HS(2); - - /* int32 nkeys = PG_GETARG_INT32(3); */ + /* HStore *query = PG_GETARG_HS(2); */ + int32 nkeys = PG_GETARG_INT32(3); /* Pointer *extra_data = (Pointer *) PG_GETARG_POINTER(4); */ bool *recheck = (bool *) PG_GETARG_POINTER(5); bool res = true; + *recheck = false; + if (strategy == HStoreContainsStrategyNumber) { int i; /* * Index lost information about correspondence of keys and values, so - * we need recheck + * we need recheck (pre-8.4 this is handled at SQL level) */ *recheck = true; - for (i = 0; res && i < 2 * query->size; i++) + for (i = 0; res && i < nkeys; i++) if (check[i] == false) res = false; } else if (strategy == HStoreExistsStrategyNumber) { /* Existence of key is guaranteed */ - *recheck = false; res = true; } + else if (strategy == HStoreExistsAnyStrategyNumber) + { + /* Existence of key is guaranteed */ + res = true; + } + else if (strategy == HStoreExistsAllStrategyNumber) + { + int i; + + for (i = 0; res && i < nkeys; ++i) + if (!check[i]) + res = false; + } else elog(ERROR, "Unsupported strategy number: %d", strategy); diff --git a/contrib/hstore/hstore_gist.c b/contrib/hstore/hstore_gist.c index 0f6eac347c..b036fa932f 100644 --- a/contrib/hstore/hstore_gist.c +++ b/contrib/hstore/hstore_gist.c @@ -1,13 +1,14 @@ /* - * $PostgreSQL: pgsql/contrib/hstore/hstore_gist.c,v 1.10 2009/06/11 14:48:51 momjian Exp $ + * $PostgreSQL: pgsql/contrib/hstore/hstore_gist.c,v 1.11 2009/09/30 19:50:22 tgl Exp $ */ #include "postgres.h" #include "access/gist.h" #include "access/itup.h" #include "access/skey.h" -#include "crc32.h" +#include "catalog/pg_type.h" +#include "crc32.h" #include "hstore.h" /* bigint defines */ @@ -114,30 +115,27 @@ ghstore_compress(PG_FUNCTION_ARGS) if (entry->leafkey) { GISTTYPE *res = (GISTTYPE *) palloc0(CALCGTSIZE(0)); - HStore *toastedval = (HStore *) DatumGetPointer(entry->key); - HStore *val = (HStore *) DatumGetPointer(PG_DETOAST_DATUM(entry->key)); - HEntry *ptr = ARRPTR(val); - char *words = STRPTR(val); + HStore *val = DatumGetHStoreP(entry->key); + HEntry *hsent = ARRPTR(val); + char *ptr = STRPTR(val); + int count = HS_COUNT(val); + int i; SET_VARSIZE(res, CALCGTSIZE(0)); - while (ptr - ARRPTR(val) < val->size) + for (i = 0; i < count; ++i) { - int h; + int h; - h = crc32_sz((char *) (words + ptr->pos), ptr->keylen); + h = crc32_sz((char *) HS_KEY(hsent,ptr,i), HS_KEYLEN(hsent,i)); HASH(GETSIGN(res), h); - if (!ptr->valisnull) + if (!HS_VALISNULL(hsent,i)) { - h = crc32_sz((char *) (words + ptr->pos + ptr->keylen), ptr->vallen); + h = crc32_sz((char *) HS_VAL(hsent,ptr,i), HS_VALLEN(hsent,i)); HASH(GETSIGN(res), h); } - ptr++; } - if (val != toastedval) - pfree(val); - retval = (GISTENTRY *) palloc(sizeof(GISTENTRY)); gistentryinit(*retval, PointerGetDatum(res), entry->rel, entry->page, @@ -177,7 +175,7 @@ ghstore_decompress(PG_FUNCTION_ARGS) GISTENTRY *retval; HStore *key; - key = (HStore *) PG_DETOAST_DATUM(entry->key); + key = DatumGetHStoreP(entry->key); if (key != (HStore *) DatumGetPointer(entry->key)) { @@ -500,7 +498,6 @@ ghstore_picksplit(PG_FUNCTION_ARGS) } *right = *left = FirstOffsetNumber; - pfree(costvector); v->spl_ldatum = PointerGetDatum(datum_l); v->spl_rdatum = PointerGetDatum(datum_r); @@ -514,7 +511,6 @@ ghstore_consistent(PG_FUNCTION_ARGS) { GISTTYPE *entry = (GISTTYPE *) DatumGetPointer(((GISTENTRY *) PG_GETARG_POINTER(0))->key); StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(2); - /* Oid subtype = PG_GETARG_OID(3); */ bool *recheck = (bool *) PG_GETARG_POINTER(4); bool res = true; @@ -528,37 +524,85 @@ ghstore_consistent(PG_FUNCTION_ARGS) sign = GETSIGN(entry); - if (strategy == HStoreContainsStrategyNumber || strategy == 13 /* hack for old strats */ ) + if (strategy == HStoreContainsStrategyNumber || + strategy == HStoreOldContainsStrategyNumber) { HStore *query = PG_GETARG_HS(1); HEntry *qe = ARRPTR(query); char *qv = STRPTR(query); + int count = HS_COUNT(query); + int i; - while (res && qe - ARRPTR(query) < query->size) + for (i = 0; res && i < count; ++i) { - int crc = crc32_sz((char *) (qv + qe->pos), qe->keylen); + int crc = crc32_sz((char *) HS_KEY(qe,qv,i), HS_KEYLEN(qe,i)); if (GETBIT(sign, HASHVAL(crc))) { - if (!qe->valisnull) + if (!HS_VALISNULL(qe,i)) { - crc = crc32_sz((char *) (qv + qe->pos + qe->keylen), qe->vallen); + crc = crc32_sz((char *) HS_VAL(qe,qv,i), HS_VALLEN(qe,i)); if (!GETBIT(sign, HASHVAL(crc))) res = false; } } else res = false; - qe++; } } else if (strategy == HStoreExistsStrategyNumber) { - text *query = PG_GETARG_TEXT_P(1); - int crc = crc32_sz(VARDATA(query), VARSIZE(query) - VARHDRSZ); + text *query = PG_GETARG_TEXT_PP(1); + int crc = crc32_sz(VARDATA_ANY(query), VARSIZE_ANY_EXHDR(query)); res = (GETBIT(sign, HASHVAL(crc))) ? true : false; } + else if (strategy == HStoreExistsAllStrategyNumber) + { + ArrayType *query = PG_GETARG_ARRAYTYPE_P(1); + Datum *key_datums; + bool *key_nulls; + int key_count; + int i; + + deconstruct_array(query, + TEXTOID, -1, false, 'i', + &key_datums, &key_nulls, &key_count); + + for (i = 0; res && i < key_count; ++i) + { + int crc; + if (key_nulls[i]) + continue; + crc = crc32_sz(VARDATA(key_datums[i]), VARSIZE(key_datums[i]) - VARHDRSZ); + if (!(GETBIT(sign, HASHVAL(crc)))) + res = FALSE; + } + } + else if (strategy == HStoreExistsAnyStrategyNumber) + { + ArrayType *query = PG_GETARG_ARRAYTYPE_P(1); + Datum *key_datums; + bool *key_nulls; + int key_count; + int i; + + deconstruct_array(query, + TEXTOID, -1, false, 'i', + &key_datums, &key_nulls, &key_count); + + res = FALSE; + + for (i = 0; !res && i < key_count; ++i) + { + int crc; + if (key_nulls[i]) + continue; + crc = crc32_sz(VARDATA(key_datums[i]), VARSIZE(key_datums[i]) - VARHDRSZ); + if (GETBIT(sign, HASHVAL(crc))) + res = TRUE; + } + } else elog(ERROR, "Unsupported strategy number: %d", strategy); diff --git a/contrib/hstore/hstore_io.c b/contrib/hstore/hstore_io.c index 3b19702520..a79cddef0a 100644 --- a/contrib/hstore/hstore_io.c +++ b/contrib/hstore/hstore_io.c @@ -1,14 +1,26 @@ /* - * $PostgreSQL: pgsql/contrib/hstore/hstore_io.c,v 1.11 2009/06/11 14:48:51 momjian Exp $ + * $PostgreSQL: pgsql/contrib/hstore/hstore_io.c,v 1.12 2009/09/30 19:50:22 tgl Exp $ */ #include "postgres.h" #include +#include "access/heapam.h" +#include "access/htup.h" +#include "catalog/pg_type.h" +#include "funcapi.h" +#include "libpq/pqformat.h" +#include "utils/lsyscache.h" +#include "utils/typcache.h" + #include "hstore.h" PG_MODULE_MAGIC; +/* old names for C functions */ +HSTORE_POLLUTE(hstore_from_text,tconvert); + + typedef struct { char *begin; @@ -263,7 +275,7 @@ parse_hstore(HSParser *state) } } -int +static int comparePairs(const void *a, const void *b) { if (((Pairs *) a)->keylen == ((Pairs *) b)->keylen) @@ -286,8 +298,14 @@ comparePairs(const void *a, const void *b) return (((Pairs *) a)->keylen > ((Pairs *) b)->keylen) ? 1 : -1; } +/* + * this code still respects pairs.needfree, even though in general + * it should never be called in a context where anything needs freeing. + * we keep it because (a) those calls are in a rare code path anyway, + * and (b) who knows whether they might be needed by some caller. + */ int -uniquePairs(Pairs *a, int4 l, int4 *buflen) +hstoreUniquePairs(Pairs *a, int4 l, int4 *buflen) { Pairs *ptr, *res; @@ -305,7 +323,8 @@ uniquePairs(Pairs *a, int4 l, int4 *buflen) res = a; while (ptr - a < l) { - if (ptr->keylen == res->keylen && strncmp(ptr->key, res->key, res->keylen) == 0) + if (ptr->keylen == res->keylen && + strncmp(ptr->key, res->key, res->keylen) == 0) { if (ptr->needfree) { @@ -327,24 +346,6 @@ uniquePairs(Pairs *a, int4 l, int4 *buflen) return res + 1 - a; } -static void -freeHSParse(HSParser *state) -{ - int i; - - if (state->word) - pfree(state->word); - for (i = 0; i < state->pcur; i++) - if (state->pairs[i].needfree) - { - if (state->pairs[i].key) - pfree(state->pairs[i].key); - if (state->pairs[i].val) - pfree(state->pairs[i].val); - } - pfree(state->pairs); -} - size_t hstoreCheckKeyLen(size_t len) { @@ -366,65 +367,722 @@ hstoreCheckValLen(size_t len) } +HStore * +hstorePairs(Pairs *pairs, int4 pcount, int4 buflen) +{ + HStore *out; + HEntry *entry; + char *ptr; + char *buf; + int4 len; + int4 i; + + len = CALCDATASIZE(pcount, buflen); + out = palloc(len); + SET_VARSIZE(out, len); + HS_SETCOUNT(out, pcount); + + if (pcount == 0) + return out; + + entry = ARRPTR(out); + buf = ptr = STRPTR(out); + + for (i = 0; i < pcount; i++) + HS_ADDITEM(entry,buf,ptr,pairs[i]); + + HS_FINALIZE(out,pcount,buf,ptr); + + return out; +} + + PG_FUNCTION_INFO_V1(hstore_in); Datum hstore_in(PG_FUNCTION_ARGS); Datum hstore_in(PG_FUNCTION_ARGS) { HSParser state; - int4 len, - buflen, - i; + int4 buflen; HStore *out; - HEntry *entries; - char *ptr; state.begin = PG_GETARG_CSTRING(0); parse_hstore(&state); - if (state.pcur == 0) + state.pcur = hstoreUniquePairs(state.pairs, state.pcur, &buflen); + + out = hstorePairs(state.pairs, state.pcur, buflen); + + PG_RETURN_POINTER(out); +} + + +PG_FUNCTION_INFO_V1(hstore_recv); +Datum hstore_recv(PG_FUNCTION_ARGS); +Datum +hstore_recv(PG_FUNCTION_ARGS) +{ + int4 buflen; + HStore *out; + Pairs *pairs; + int4 i; + int4 pcount; + StringInfo buf = (StringInfo) PG_GETARG_POINTER(0); + + pcount = pq_getmsgint(buf, 4); + + if (pcount == 0) { - freeHSParse(&state); - len = CALCDATASIZE(0, 0); - out = palloc(len); - SET_VARSIZE(out, len); - out->size = 0; + out = hstorePairs(NULL, 0, 0); PG_RETURN_POINTER(out); } - state.pcur = uniquePairs(state.pairs, state.pcur, &buflen); + pairs = palloc(pcount * sizeof(Pairs)); - len = CALCDATASIZE(state.pcur, buflen); - out = palloc(len); - SET_VARSIZE(out, len); - out->size = state.pcur; + for (i = 0; i < pcount; ++i) + { + int rawlen = pq_getmsgint(buf, 4); + int len; + + if (rawlen < 0) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("null value not allowed for hstore key"))); + + pairs[i].key = pq_getmsgtext(buf, rawlen, &len); + pairs[i].keylen = hstoreCheckKeyLen(len); + pairs[i].needfree = true; + + rawlen = pq_getmsgint(buf, 4); + if (rawlen < 0) + { + pairs[i].val = NULL; + pairs[i].vallen = 0; + pairs[i].isnull = true; + } + else + { + pairs[i].val = pq_getmsgtext(buf, rawlen, &len); + pairs[i].vallen = hstoreCheckValLen(len); + pairs[i].isnull = false; + } + } + + pcount = hstoreUniquePairs(pairs, pcount, &buflen); + + out = hstorePairs(pairs, pcount, buflen); + + PG_RETURN_POINTER(out); +} + + +PG_FUNCTION_INFO_V1(hstore_from_text); +Datum hstore_from_text(PG_FUNCTION_ARGS); +Datum +hstore_from_text(PG_FUNCTION_ARGS) +{ + text *key; + text *val = NULL; + Pairs p; + HStore *out; + + if (PG_ARGISNULL(0)) + PG_RETURN_NULL(); + + p.needfree = false; + key = PG_GETARG_TEXT_PP(0); + p.key = VARDATA_ANY(key); + p.keylen = hstoreCheckKeyLen(VARSIZE_ANY_EXHDR(key)); + + if (PG_ARGISNULL(1)) + { + p.vallen = 0; + p.isnull = true; + } + else + { + val = PG_GETARG_TEXT_PP(1); + p.val = VARDATA_ANY(val); + p.vallen = hstoreCheckValLen(VARSIZE_ANY_EXHDR(val)); + p.isnull = false; + } + + out = hstorePairs(&p, 1, p.keylen + p.vallen); + + PG_RETURN_POINTER(out); +} + + +PG_FUNCTION_INFO_V1(hstore_from_arrays); +Datum hstore_from_arrays(PG_FUNCTION_ARGS); +Datum +hstore_from_arrays(PG_FUNCTION_ARGS) +{ + int4 buflen; + HStore *out; + Pairs *pairs; + Datum *key_datums; + bool *key_nulls; + int key_count; + Datum *value_datums; + bool *value_nulls; + int value_count; + ArrayType *key_array; + ArrayType *value_array; + int i; + + if (PG_ARGISNULL(0)) + PG_RETURN_NULL(); + + key_array = PG_GETARG_ARRAYTYPE_P(0); + + Assert(ARR_ELEMTYPE(key_array) == TEXTOID); + + /* + * must check >1 rather than != 1 because empty arrays have + * 0 dimensions, not 1 + */ - entries = ARRPTR(out); - ptr = STRPTR(out); + if (ARR_NDIM(key_array) > 1) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("wrong number of array subscripts"))); + + deconstruct_array(key_array, + TEXTOID, -1, false, 'i', + &key_datums, &key_nulls, &key_count); + + /* value_array might be NULL */ - for (i = 0; i < out->size; i++) + if (PG_ARGISNULL(1)) + { + value_array = NULL; + value_count = key_count; + value_datums = NULL; + value_nulls = NULL; + } + else { - entries[i].keylen = state.pairs[i].keylen; - entries[i].pos = ptr - STRPTR(out); - memcpy(ptr, state.pairs[i].key, state.pairs[i].keylen); - ptr += entries[i].keylen; + value_array = PG_GETARG_ARRAYTYPE_P(1); + + Assert(ARR_ELEMTYPE(value_array) == TEXTOID); + + if (ARR_NDIM(value_array) > 1) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("wrong number of array subscripts"))); + + if ((ARR_NDIM(key_array) > 0 || ARR_NDIM(value_array) > 0) && + (ARR_NDIM(key_array) != ARR_NDIM(value_array) || + ARR_DIMS(key_array)[0] != ARR_DIMS(value_array)[0] || + ARR_LBOUND(key_array)[0] != ARR_LBOUND(value_array)[0])) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("arrays must have same bounds"))); + + deconstruct_array(value_array, + TEXTOID, -1, false, 'i', + &value_datums, &value_nulls, &value_count); - entries[i].valisnull = state.pairs[i].isnull; - if (entries[i].valisnull) - entries[i].vallen = 4; /* null */ + Assert(key_count == value_count); + } + + pairs = palloc(key_count * sizeof(Pairs)); + + for (i = 0; i < key_count; ++i) + { + if (key_nulls[i]) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("null value not allowed for hstore key"))); + + if (!value_nulls || value_nulls[i]) + { + pairs[i].key = VARDATA_ANY(key_datums[i]); + pairs[i].val = NULL; + pairs[i].keylen = hstoreCheckKeyLen(VARSIZE_ANY_EXHDR(key_datums[i])); + pairs[i].vallen = 4; + pairs[i].isnull = true; + pairs[i].needfree = false; + } else { - entries[i].vallen = state.pairs[i].vallen; - memcpy(ptr, state.pairs[i].val, state.pairs[i].vallen); - ptr += entries[i].vallen; + pairs[i].key = VARDATA_ANY(key_datums[i]); + pairs[i].val = VARDATA_ANY(value_datums[i]); + pairs[i].keylen = hstoreCheckKeyLen(VARSIZE_ANY_EXHDR(key_datums[i])); + pairs[i].vallen = hstoreCheckValLen(VARSIZE_ANY_EXHDR(value_datums[i])); + pairs[i].isnull = false; + pairs[i].needfree = false; + } + } + + key_count = hstoreUniquePairs(pairs, key_count, &buflen); + + out = hstorePairs(pairs, key_count, buflen); + + PG_RETURN_POINTER(out); +} + + +PG_FUNCTION_INFO_V1(hstore_from_array); +Datum hstore_from_array(PG_FUNCTION_ARGS); +Datum +hstore_from_array(PG_FUNCTION_ARGS) +{ + ArrayType *in_array = PG_GETARG_ARRAYTYPE_P(0); + int ndims = ARR_NDIM(in_array); + int count; + int4 buflen; + HStore *out; + Pairs *pairs; + Datum *in_datums; + bool *in_nulls; + int in_count; + int i; + + Assert(ARR_ELEMTYPE(in_array) == TEXTOID); + + switch (ndims) + { + case 0: + out = hstorePairs(NULL, 0, 0); + PG_RETURN_POINTER(out); + + case 1: + if ((ARR_DIMS(in_array)[0]) % 2) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("array must have even number of elements"))); + break; + + case 2: + if ((ARR_DIMS(in_array)[1]) != 2) + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("array must have two columns"))); + break; + + default: + ereport(ERROR, + (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), + errmsg("wrong number of array subscripts"))); + } + + deconstruct_array(in_array, + TEXTOID, -1, false, 'i', + &in_datums, &in_nulls, &in_count); + + count = in_count / 2; + + pairs = palloc(count * sizeof(Pairs)); + + for (i = 0; i < count; ++i) + { + if (in_nulls[i*2]) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("null value not allowed for hstore key"))); + + if (in_nulls[i*2+1]) + { + pairs[i].key = VARDATA_ANY(in_datums[i*2]); + pairs[i].val = NULL; + pairs[i].keylen = hstoreCheckKeyLen(VARSIZE_ANY_EXHDR(in_datums[i*2])); + pairs[i].vallen = 4; + pairs[i].isnull = true; + pairs[i].needfree = false; + } + else + { + pairs[i].key = VARDATA_ANY(in_datums[i*2]); + pairs[i].val = VARDATA_ANY(in_datums[i*2+1]); + pairs[i].keylen = hstoreCheckKeyLen(VARSIZE_ANY_EXHDR(in_datums[i*2])); + pairs[i].vallen = hstoreCheckValLen(VARSIZE_ANY_EXHDR(in_datums[i*2+1])); + pairs[i].isnull = false; + pairs[i].needfree = false; + } + } + + count = hstoreUniquePairs(pairs, count, &buflen); + + out = hstorePairs(pairs, count, buflen); + + PG_RETURN_POINTER(out); +} + +/* most of hstore_from_record is shamelessly swiped from record_out */ + +/* + * structure to cache metadata needed for record I/O + */ +typedef struct ColumnIOData +{ + Oid column_type; + Oid typiofunc; + Oid typioparam; + FmgrInfo proc; +} ColumnIOData; + +typedef struct RecordIOData +{ + Oid record_type; + int32 record_typmod; + int ncolumns; + ColumnIOData columns[1]; /* VARIABLE LENGTH ARRAY */ +} RecordIOData; + +PG_FUNCTION_INFO_V1(hstore_from_record); +Datum hstore_from_record(PG_FUNCTION_ARGS); +Datum +hstore_from_record(PG_FUNCTION_ARGS) +{ + HeapTupleHeader rec; + int4 buflen; + HStore *out; + Pairs *pairs; + Oid tupType; + int32 tupTypmod; + TupleDesc tupdesc; + HeapTupleData tuple; + RecordIOData *my_extra; + int ncolumns; + int i,j; + Datum *values; + bool *nulls; + + if (PG_ARGISNULL(0)) + { + Oid argtype = get_fn_expr_argtype(fcinfo->flinfo,0); + + /* + * have no tuple to look at, so the only source of type info + * is the argtype. The lookup_rowtype_tupdesc call below will + * error out if we don't have a known composite type oid here. + */ + tupType = argtype; + tupTypmod = -1; + + rec = NULL; + } + else + { + rec = PG_GETARG_HEAPTUPLEHEADER(0); + + /* Extract type info from the tuple itself */ + tupType = HeapTupleHeaderGetTypeId(rec); + tupTypmod = HeapTupleHeaderGetTypMod(rec); + } + + tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod); + ncolumns = tupdesc->natts; + + /* + * We arrange to look up the needed I/O info just once per series of + * calls, assuming the record type doesn't change underneath us. + */ + my_extra = (RecordIOData *) fcinfo->flinfo->fn_extra; + if (my_extra == NULL || + my_extra->ncolumns != ncolumns) + { + fcinfo->flinfo->fn_extra = + MemoryContextAlloc(fcinfo->flinfo->fn_mcxt, + sizeof(RecordIOData) - sizeof(ColumnIOData) + + ncolumns * sizeof(ColumnIOData)); + my_extra = (RecordIOData *) fcinfo->flinfo->fn_extra; + my_extra->record_type = InvalidOid; + my_extra->record_typmod = 0; + } + + if (my_extra->record_type != tupType || + my_extra->record_typmod != tupTypmod) + { + MemSet(my_extra, 0, + sizeof(RecordIOData) - sizeof(ColumnIOData) + + ncolumns * sizeof(ColumnIOData)); + my_extra->record_type = tupType; + my_extra->record_typmod = tupTypmod; + my_extra->ncolumns = ncolumns; + } + + pairs = palloc(ncolumns * sizeof(Pairs)); + + if (rec) + { + /* Build a temporary HeapTuple control structure */ + tuple.t_len = HeapTupleHeaderGetDatumLength(rec); + ItemPointerSetInvalid(&(tuple.t_self)); + tuple.t_tableOid = InvalidOid; + tuple.t_data = rec; + + values = (Datum *) palloc(ncolumns * sizeof(Datum)); + nulls = (bool *) palloc(ncolumns * sizeof(bool)); + + /* Break down the tuple into fields */ + heap_deform_tuple(&tuple, tupdesc, values, nulls); + } + else + { + values = NULL; + nulls = NULL; + } + + for (i = 0, j = 0; i < ncolumns; ++i) + { + ColumnIOData *column_info = &my_extra->columns[i]; + Oid column_type = tupdesc->attrs[i]->atttypid; + char *value; + + /* Ignore dropped columns in datatype */ + if (tupdesc->attrs[i]->attisdropped) + continue; + + pairs[j].key = NameStr(tupdesc->attrs[i]->attname); + pairs[j].keylen = hstoreCheckKeyLen(strlen(NameStr(tupdesc->attrs[i]->attname))); + + if (!nulls || nulls[i]) + { + pairs[j].val = NULL; + pairs[j].vallen = 4; + pairs[j].isnull = true; + pairs[j].needfree = false; + ++j; + continue; } + + /* + * Convert the column value to text + */ + if (column_info->column_type != column_type) + { + bool typIsVarlena; + + getTypeOutputInfo(column_type, + &column_info->typiofunc, + &typIsVarlena); + fmgr_info_cxt(column_info->typiofunc, &column_info->proc, + fcinfo->flinfo->fn_mcxt); + column_info->column_type = column_type; + } + + value = OutputFunctionCall(&column_info->proc, values[i]); + + pairs[j].val = value; + pairs[j].vallen = hstoreCheckValLen(strlen(value)); + pairs[j].isnull = false; + pairs[j].needfree = false; + ++j; } - freeHSParse(&state); + ncolumns = hstoreUniquePairs(pairs, j, &buflen); + + out = hstorePairs(pairs, ncolumns, buflen); + + ReleaseTupleDesc(tupdesc); + PG_RETURN_POINTER(out); } + +PG_FUNCTION_INFO_V1(hstore_populate_record); +Datum hstore_populate_record(PG_FUNCTION_ARGS); +Datum +hstore_populate_record(PG_FUNCTION_ARGS) +{ + Oid argtype = get_fn_expr_argtype(fcinfo->flinfo,0); + HStore *hs; + HEntry *entries; + char *ptr; + HeapTupleHeader rec; + Oid tupType; + int32 tupTypmod; + TupleDesc tupdesc; + HeapTupleData tuple; + HeapTuple rettuple; + RecordIOData *my_extra; + int ncolumns; + int i; + Datum *values; + bool *nulls; + + if (!type_is_rowtype(argtype)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("first argument must be a rowtype"))); + + if (PG_ARGISNULL(0)) + { + if (PG_ARGISNULL(1)) + PG_RETURN_NULL(); + + rec = NULL; + + /* + * have no tuple to look at, so the only source of type info + * is the argtype. The lookup_rowtype_tupdesc call below will + * error out if we don't have a known composite type oid here. + */ + tupType = argtype; + tupTypmod = -1; + } + else + { + rec = PG_GETARG_HEAPTUPLEHEADER(0); + + if (PG_ARGISNULL(1)) + PG_RETURN_POINTER(rec); + + /* Extract type info from the tuple itself */ + tupType = HeapTupleHeaderGetTypeId(rec); + tupTypmod = HeapTupleHeaderGetTypMod(rec); + } + + hs = PG_GETARG_HS(1); + entries = ARRPTR(hs); + ptr = STRPTR(hs); + + /* + * if the input hstore is empty, we can only skip the rest if + * we were passed in a non-null record, since otherwise there + * may be issues with domain nulls. + */ + + if (HS_COUNT(hs) == 0 && rec) + PG_RETURN_POINTER(rec); + + tupdesc = lookup_rowtype_tupdesc(tupType, tupTypmod); + ncolumns = tupdesc->natts; + + if (rec) + { + /* Build a temporary HeapTuple control structure */ + tuple.t_len = HeapTupleHeaderGetDatumLength(rec); + ItemPointerSetInvalid(&(tuple.t_self)); + tuple.t_tableOid = InvalidOid; + tuple.t_data = rec; + } + + /* + * We arrange to look up the needed I/O info just once per series of + * calls, assuming the record type doesn't change underneath us. + */ + my_extra = (RecordIOData *) fcinfo->flinfo->fn_extra; + if (my_extra == NULL || + my_extra->ncolumns != ncolumns) + { + fcinfo->flinfo->fn_extra = + MemoryContextAlloc(fcinfo->flinfo->fn_mcxt, + sizeof(RecordIOData) - sizeof(ColumnIOData) + + ncolumns * sizeof(ColumnIOData)); + my_extra = (RecordIOData *) fcinfo->flinfo->fn_extra; + my_extra->record_type = InvalidOid; + my_extra->record_typmod = 0; + } + + if (my_extra->record_type != tupType || + my_extra->record_typmod != tupTypmod) + { + MemSet(my_extra, 0, + sizeof(RecordIOData) - sizeof(ColumnIOData) + + ncolumns * sizeof(ColumnIOData)); + my_extra->record_type = tupType; + my_extra->record_typmod = tupTypmod; + my_extra->ncolumns = ncolumns; + } + + values = (Datum *) palloc(ncolumns * sizeof(Datum)); + nulls = (bool *) palloc(ncolumns * sizeof(bool)); + + if (rec) + { + /* Break down the tuple into fields */ + heap_deform_tuple(&tuple, tupdesc, values, nulls); + } + else + { + for (i = 0; i < ncolumns; ++i) + { + values[i] = (Datum) 0; + nulls[i] = true; + } + } + + for (i = 0; i < ncolumns; ++i) + { + ColumnIOData *column_info = &my_extra->columns[i]; + Oid column_type = tupdesc->attrs[i]->atttypid; + char *value; + int idx; + int vallen; + + /* Ignore dropped columns in datatype */ + if (tupdesc->attrs[i]->attisdropped) + { + nulls[i] = true; + continue; + } + + idx = hstoreFindKey(hs, 0, + NameStr(tupdesc->attrs[i]->attname), + strlen(NameStr(tupdesc->attrs[i]->attname))); + /* + * we can't just skip here if the key wasn't found since we + * might have a domain to deal with. If we were passed in a + * non-null record datum, we assume that the existing values + * are valid (if they're not, then it's not our fault), but if + * we were passed in a null, then every field which we don't + * populate needs to be run through the input function just in + * case it's a domain type. + */ + if (idx < 0 && rec) + continue; + + /* + * Prepare to convert the column value from text + */ + if (column_info->column_type != column_type) + { + getTypeInputInfo(column_type, + &column_info->typiofunc, + &column_info->typioparam); + fmgr_info_cxt(column_info->typiofunc, &column_info->proc, + fcinfo->flinfo->fn_mcxt); + column_info->column_type = column_type; + } + + if (idx < 0 || HS_VALISNULL(entries,idx)) + { + /* + * need InputFunctionCall to happen even for nulls, so + * that domain checks are done + */ + values[i] = InputFunctionCall(&column_info->proc, NULL, + column_info->typioparam, + tupdesc->attrs[i]->atttypmod); + nulls[i] = true; + } + else + { + vallen = HS_VALLEN(entries,idx); + value = palloc(1 + vallen); + memcpy(value, HS_VAL(entries,ptr,idx), vallen); + value[vallen] = 0; + + values[i] = InputFunctionCall(&column_info->proc, value, + column_info->typioparam, + tupdesc->attrs[i]->atttypmod); + nulls[i] = false; + } + } + + rettuple = heap_form_tuple(tupdesc, values, nulls); + + ReleaseTupleDesc(tupdesc); + + PG_RETURN_DATUM(HeapTupleGetDatum(rettuple)); +} + + static char * cpw(char *dst, char *src, int len) { @@ -446,40 +1104,50 @@ hstore_out(PG_FUNCTION_ARGS) { HStore *in = PG_GETARG_HS(0); int buflen, - i, - nnulls = 0; + i; + int count = HS_COUNT(in); char *out, *ptr; char *base = STRPTR(in); HEntry *entries = ARRPTR(in); - if (in->size == 0) + if (count == 0) { out = palloc(1); *out = '\0'; - PG_FREE_IF_COPY(in, 0); PG_RETURN_CSTRING(out); } - for (i = 0; i < in->size; i++) - if (entries[i].valisnull) - nnulls++; + buflen = 0; + + /* + * this loop overestimates due to pessimistic assumptions about + * escaping, so very large hstore values can't be output. this + * could be fixed, but many other data types probably have the + * same issue. This replaced code that used the original varlena + * size for calculations, which was wrong in some subtle ways. + */ - buflen = (4 /* " */ + 2 /* => */ ) * (in->size - nnulls) + - (2 /* " */ + 2 /* => */ + 4 /* NULL */ ) * nnulls + - 2 /* , */ * (in->size - 1) + - 2 /* esc */ * (VARSIZE(in) - CALCDATASIZE(in->size, 0)) + - 1 /* \0 */ ; + for (i = 0; i < count; i++) + { + /* include "" and => and comma-space */ + buflen += 6 + 2 * HS_KEYLEN(entries,i); + /* include "" only if nonnull */ + buflen += 2 + (HS_VALISNULL(entries,i) + ? 2 + : 2 * HS_VALLEN(entries,i)); + } out = ptr = palloc(buflen); - for (i = 0; i < in->size; i++) + + for (i = 0; i < count; i++) { *ptr++ = '"'; - ptr = cpw(ptr, base + entries[i].pos, entries[i].keylen); + ptr = cpw(ptr, HS_KEY(entries,base,i), HS_KEYLEN(entries,i)); *ptr++ = '"'; *ptr++ = '='; *ptr++ = '>'; - if (entries[i].valisnull) + if (HS_VALISNULL(entries,i)) { *ptr++ = 'N'; *ptr++ = 'U'; @@ -489,11 +1157,11 @@ hstore_out(PG_FUNCTION_ARGS) else { *ptr++ = '"'; - ptr = cpw(ptr, base + entries[i].pos + entries[i].keylen, entries[i].vallen); + ptr = cpw(ptr, HS_VAL(entries,base,i), HS_VALLEN(entries,i)); *ptr++ = '"'; } - if (i + 1 != in->size) + if (i + 1 != count) { *ptr++ = ','; *ptr++ = ' '; @@ -501,6 +1169,42 @@ hstore_out(PG_FUNCTION_ARGS) } *ptr = '\0'; - PG_FREE_IF_COPY(in, 0); PG_RETURN_CSTRING(out); } + + +PG_FUNCTION_INFO_V1(hstore_send); +Datum hstore_send(PG_FUNCTION_ARGS); +Datum +hstore_send(PG_FUNCTION_ARGS) +{ + HStore *in = PG_GETARG_HS(0); + int i; + int count = HS_COUNT(in); + char *base = STRPTR(in); + HEntry *entries = ARRPTR(in); + StringInfoData buf; + + pq_begintypsend(&buf); + + pq_sendint(&buf, count, 4); + + for (i = 0; i < count; i++) + { + int32 keylen = HS_KEYLEN(entries,i); + pq_sendint(&buf, keylen, 4); + pq_sendtext(&buf, HS_KEY(entries,base,i), keylen); + if (HS_VALISNULL(entries,i)) + { + pq_sendint(&buf, -1, 4); + } + else + { + int32 vallen = HS_VALLEN(entries,i); + pq_sendint(&buf, vallen, 4); + pq_sendtext(&buf, HS_VAL(entries,base,i), vallen); + } + } + + PG_RETURN_BYTEA_P(pq_endtypsend(&buf)); +} diff --git a/contrib/hstore/hstore_op.c b/contrib/hstore/hstore_op.c index 8d471e30f1..2338bbf44b 100644 --- a/contrib/hstore/hstore_op.c +++ b/contrib/hstore/hstore_op.c @@ -1,498 +1,935 @@ /* - * $PostgreSQL + * $PostgreSQL: pgsql/contrib/hstore/hstore_op.c,v 1.14 2009/09/30 19:50:22 tgl Exp $ */ #include "postgres.h" +#include "access/hash.h" +#include "access/heapam.h" +#include "access/htup.h" #include "catalog/pg_type.h" #include "funcapi.h" -#include "utils/array.h" #include "utils/builtins.h" #include "hstore.h" +/* old names for C functions */ +HSTORE_POLLUTE(hstore_fetchval,fetchval); +HSTORE_POLLUTE(hstore_exists,exists); +HSTORE_POLLUTE(hstore_defined,defined); +HSTORE_POLLUTE(hstore_delete,delete); +HSTORE_POLLUTE(hstore_concat,hs_concat); +HSTORE_POLLUTE(hstore_contains,hs_contains); +HSTORE_POLLUTE(hstore_contained,hs_contained); +HSTORE_POLLUTE(hstore_akeys,akeys); +HSTORE_POLLUTE(hstore_avals,avals); +HSTORE_POLLUTE(hstore_skeys,skeys); +HSTORE_POLLUTE(hstore_svals,svals); +HSTORE_POLLUTE(hstore_each,each); -static HEntry * -findkey(HStore *hs, char *key, int keylen) + +/* + * We're often finding a sequence of keys in ascending order. The + * "lowbound" parameter is used to cache lower bounds of searches + * between calls, based on this assumption. Pass NULL for it for + * one-off or unordered searches. + */ +int +hstoreFindKey(HStore * hs, int *lowbound, char *key, int keylen) { - HEntry *StopLow = ARRPTR(hs); - HEntry *StopHigh = StopLow + hs->size; - HEntry *StopMiddle; - int difference; + HEntry *entries = ARRPTR(hs); + int stopLow = lowbound ? *lowbound : 0; + int stopHigh = HS_COUNT(hs); + int stopMiddle; char *base = STRPTR(hs); - while (StopLow < StopHigh) + while (stopLow < stopHigh) { - StopMiddle = StopLow + (StopHigh - StopLow) / 2; + int difference; - if (StopMiddle->keylen == keylen) - difference = strncmp(base + StopMiddle->pos, key, StopMiddle->keylen); + stopMiddle = stopLow + (stopHigh - stopLow) / 2; + + if (HS_KEYLEN(entries,stopMiddle) == keylen) + difference = strncmp(HS_KEY(entries,base,stopMiddle), key, keylen); else - difference = (StopMiddle->keylen > keylen) ? 1 : -1; + difference = (HS_KEYLEN(entries,stopMiddle) > keylen) ? 1 : -1; if (difference == 0) - return StopMiddle; + { + if (lowbound) + *lowbound = stopMiddle + 1; + return stopMiddle; + } else if (difference < 0) - StopLow = StopMiddle + 1; + stopLow = stopMiddle + 1; else - StopHigh = StopMiddle; + stopHigh = stopMiddle; } - return NULL; + if (lowbound) + *lowbound = stopLow; + return -1; } -PG_FUNCTION_INFO_V1(fetchval); -Datum fetchval(PG_FUNCTION_ARGS); +Pairs * +hstoreArrayToPairs(ArrayType *a, int *npairs) +{ + Datum *key_datums; + bool *key_nulls; + int key_count; + Pairs *key_pairs; + int bufsiz; + int i,j; + + deconstruct_array(a, + TEXTOID, -1, false, 'i', + &key_datums, &key_nulls, &key_count); + + if (key_count == 0) + { + *npairs = 0; + return NULL; + } + + key_pairs = palloc(sizeof(Pairs) * key_count); + + for (i = 0, j = 0; i < key_count; i++) + { + if (!key_nulls[i]) + { + key_pairs[j].key = VARDATA(key_datums[i]); + key_pairs[j].keylen = VARSIZE(key_datums[i]) - VARHDRSZ; + key_pairs[j].val = NULL; + key_pairs[j].vallen = 0; + key_pairs[j].needfree = 0; + key_pairs[j].isnull = 1; + j++; + } + } + + *npairs = hstoreUniquePairs(key_pairs, j, &bufsiz); + + return key_pairs; +} + + +PG_FUNCTION_INFO_V1(hstore_fetchval); +Datum hstore_fetchval(PG_FUNCTION_ARGS); Datum -fetchval(PG_FUNCTION_ARGS) +hstore_fetchval(PG_FUNCTION_ARGS) { HStore *hs = PG_GETARG_HS(0); - text *key = PG_GETARG_TEXT_P(1); - HEntry *entry; + text *key = PG_GETARG_TEXT_PP(1); + HEntry *entries = ARRPTR(hs); text *out; + int idx = hstoreFindKey(hs, NULL, + VARDATA_ANY(key), VARSIZE_ANY_EXHDR(key)); - if ((entry = findkey(hs, VARDATA(key), VARSIZE(key) - VARHDRSZ)) == NULL || entry->valisnull) - { - PG_FREE_IF_COPY(hs, 0); - PG_FREE_IF_COPY(key, 1); + if (idx < 0 || HS_VALISNULL(entries,idx)) PG_RETURN_NULL(); - } - out = cstring_to_text_with_len(STRPTR(hs) + entry->pos + entry->keylen, - entry->vallen); + out = cstring_to_text_with_len(HS_VAL(entries,STRPTR(hs),idx), + HS_VALLEN(entries,idx)); - PG_FREE_IF_COPY(hs, 0); - PG_FREE_IF_COPY(key, 1); PG_RETURN_TEXT_P(out); } -PG_FUNCTION_INFO_V1(exists); -Datum exists(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_exists); +Datum hstore_exists(PG_FUNCTION_ARGS); Datum -exists(PG_FUNCTION_ARGS) +hstore_exists(PG_FUNCTION_ARGS) { HStore *hs = PG_GETARG_HS(0); - text *key = PG_GETARG_TEXT_P(1); - HEntry *entry; + text *key = PG_GETARG_TEXT_PP(1); + int idx = hstoreFindKey(hs, NULL, + VARDATA_ANY(key), VARSIZE_ANY_EXHDR(key)); - entry = findkey(hs, VARDATA(key), VARSIZE(key) - VARHDRSZ); + PG_RETURN_BOOL(idx >= 0); +} - PG_FREE_IF_COPY(hs, 0); - PG_FREE_IF_COPY(key, 1); - PG_RETURN_BOOL(entry); +PG_FUNCTION_INFO_V1(hstore_exists_any); +Datum hstore_exists_any(PG_FUNCTION_ARGS); +Datum +hstore_exists_any(PG_FUNCTION_ARGS) +{ + HStore *hs = PG_GETARG_HS(0); + ArrayType *keys = PG_GETARG_ARRAYTYPE_P(1); + int nkeys; + Pairs *key_pairs = hstoreArrayToPairs(keys, &nkeys); + int i; + int lowbound = 0; + bool res = false; + + /* + * we exploit the fact that the pairs list is already sorted into + * strictly increasing order to narrow the hstoreFindKey search; + * each search can start one entry past the previous "found" + * entry, or at the lower bound of the last search. + */ + + for (i = 0; !res && i < nkeys; ++i) + { + int idx = hstoreFindKey(hs, &lowbound, + key_pairs[i].key, key_pairs[i].keylen); + + if (idx >= 0) + res = true; + } + + PG_RETURN_BOOL(res); } -PG_FUNCTION_INFO_V1(defined); -Datum defined(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_exists_all); +Datum hstore_exists_all(PG_FUNCTION_ARGS); Datum -defined(PG_FUNCTION_ARGS) +hstore_exists_all(PG_FUNCTION_ARGS) { HStore *hs = PG_GETARG_HS(0); - text *key = PG_GETARG_TEXT_P(1); - HEntry *entry; - bool res; + ArrayType *keys = PG_GETARG_ARRAYTYPE_P(1); + int nkeys; + Pairs *key_pairs = hstoreArrayToPairs(keys, &nkeys); + int i; + int lowbound = 0; + bool res = nkeys ? true : false; + + /* + * we exploit the fact that the pairs list is already sorted into + * strictly increasing order to narrow the hstoreFindKey search; + * each search can start one entry past the previous "found" + * entry, or at the lower bound of the last search. + */ + + for (i = 0; res && i < nkeys; ++i) + { + int idx = hstoreFindKey(hs, &lowbound, + key_pairs[i].key, key_pairs[i].keylen); + + if (idx < 0) + res = false; + } - entry = findkey(hs, VARDATA(key), VARSIZE(key) - VARHDRSZ); + PG_RETURN_BOOL(res); +} - res = (entry && !entry->valisnull) ? true : false; - PG_FREE_IF_COPY(hs, 0); - PG_FREE_IF_COPY(key, 1); +PG_FUNCTION_INFO_V1(hstore_defined); +Datum hstore_defined(PG_FUNCTION_ARGS); +Datum +hstore_defined(PG_FUNCTION_ARGS) +{ + HStore *hs = PG_GETARG_HS(0); + text *key = PG_GETARG_TEXT_PP(1); + HEntry *entries = ARRPTR(hs); + int idx = hstoreFindKey(hs, NULL, + VARDATA_ANY(key), VARSIZE_ANY_EXHDR(key)); + bool res = (idx >= 0 && !HS_VALISNULL(entries,idx)); PG_RETURN_BOOL(res); } -PG_FUNCTION_INFO_V1(delete); -Datum delete(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_delete); +Datum hstore_delete(PG_FUNCTION_ARGS); Datum -delete(PG_FUNCTION_ARGS) +hstore_delete(PG_FUNCTION_ARGS) { HStore *hs = PG_GETARG_HS(0); - text *key = PG_GETARG_TEXT_P(1); + text *key = PG_GETARG_TEXT_PP(1); + char *keyptr = VARDATA_ANY(key); + int keylen = VARSIZE_ANY_EXHDR(key); HStore *out = palloc(VARSIZE(hs)); - char *ptrs, + char *bufs, + *bufd, *ptrd; HEntry *es, *ed; + int i; + int count = HS_COUNT(hs); + int outcount = 0; SET_VARSIZE(out, VARSIZE(hs)); - out->size = hs->size; /* temporary! */ + HS_SETCOUNT(out, count); /* temporary! */ - ptrs = STRPTR(hs); + bufs = STRPTR(hs); es = ARRPTR(hs); - ptrd = STRPTR(out); + bufd = ptrd = STRPTR(out); ed = ARRPTR(out); - while (es - ARRPTR(hs) < hs->size) + for (i = 0; i < count; ++i) { - if (!(es->keylen == VARSIZE(key) - VARHDRSZ && strncmp(ptrs, VARDATA(key), es->keylen) == 0)) + int len = HS_KEYLEN(es,i); + char *ptrs = HS_KEY(es,bufs,i); + + if (!(len == keylen && strncmp(ptrs, keyptr, keylen) == 0)) { - memcpy(ed, es, sizeof(HEntry)); - memcpy(ptrd, ptrs, es->keylen + ((es->valisnull) ? 0 : es->vallen)); - ed->pos = ptrd - STRPTR(out); - ptrd += es->keylen + ((es->valisnull) ? 0 : es->vallen); - ed++; + int vallen = HS_VALLEN(es,i); + HS_COPYITEM(ed, bufd, ptrd, ptrs, len, vallen, HS_VALISNULL(es,i)); + ++outcount; } - ptrs += es->keylen + ((es->valisnull) ? 0 : es->vallen); - es++; } - if (ed - ARRPTR(out) != out->size) + HS_FINALIZE(out,outcount,bufd,ptrd); + + PG_RETURN_POINTER(out); +} + + +PG_FUNCTION_INFO_V1(hstore_delete_array); +Datum hstore_delete_array(PG_FUNCTION_ARGS); +Datum +hstore_delete_array(PG_FUNCTION_ARGS) +{ + HStore *hs = PG_GETARG_HS(0); + HStore *out = palloc(VARSIZE(hs)); + int hs_count = HS_COUNT(hs); + char *ps, + *bufd, + *pd; + HEntry *es, + *ed; + int i,j; + int outcount = 0; + ArrayType *key_array = PG_GETARG_ARRAYTYPE_P(1); + int nkeys; + Pairs *key_pairs = hstoreArrayToPairs(key_array, &nkeys); + + SET_VARSIZE(out, VARSIZE(hs)); + HS_SETCOUNT(out, hs_count); /* temporary! */ + + ps = STRPTR(hs); + es = ARRPTR(hs); + bufd = pd = STRPTR(out); + ed = ARRPTR(out); + + if (nkeys == 0) { - int buflen = ptrd - STRPTR(out); + /* return a copy of the input, unchanged */ + memcpy(out, hs, VARSIZE(hs)); + HS_FIXSIZE(out, hs_count); + HS_SETCOUNT(out, hs_count); + PG_RETURN_POINTER(out); + } + + /* + * this is in effect a merge between hs and key_pairs, both of + * which are already sorted by (keylen,key); we take keys from + * hs only + */ + + for (i = j = 0; i < hs_count; ) + { + int difference; + + if (j >= nkeys) + difference = -1; + else + { + int skeylen = HS_KEYLEN(es,i); + if (skeylen == key_pairs[j].keylen) + difference = strncmp(HS_KEY(es,ps,i), + key_pairs[j].key, + key_pairs[j].keylen); + else + difference = (skeylen > key_pairs[j].keylen) ? 1 : -1; + } + + if (difference > 0) + ++j; + else if (difference == 0) + ++i, ++j; + else + { + HS_COPYITEM(ed, bufd, pd, + HS_KEY(es,ps,i), HS_KEYLEN(es,i), + HS_VALLEN(es,i), HS_VALISNULL(es,i)); + ++outcount; + ++i; + } + } + + HS_FINALIZE(out,outcount,bufd,pd); + + PG_RETURN_POINTER(out); +} + - ptrd = STRPTR(out); +PG_FUNCTION_INFO_V1(hstore_delete_hstore); +Datum hstore_delete_hstore(PG_FUNCTION_ARGS); +Datum +hstore_delete_hstore(PG_FUNCTION_ARGS) +{ + HStore *hs = PG_GETARG_HS(0); + HStore *hs2 = PG_GETARG_HS(1); + HStore *out = palloc(VARSIZE(hs)); + int hs_count = HS_COUNT(hs); + int hs2_count = HS_COUNT(hs2); + char *ps, + *ps2, + *bufd, + *pd; + HEntry *es, + *es2, + *ed; + int i,j; + int outcount = 0; - out->size = ed - ARRPTR(out); + SET_VARSIZE(out, VARSIZE(hs)); + HS_SETCOUNT(out, hs_count); /* temporary! */ - memmove(STRPTR(out), ptrd, buflen); - SET_VARSIZE(out, CALCDATASIZE(out->size, buflen)); + ps = STRPTR(hs); + es = ARRPTR(hs); + ps2 = STRPTR(hs2); + es2 = ARRPTR(hs2); + bufd = pd = STRPTR(out); + ed = ARRPTR(out); + + if (hs2_count == 0) + { + /* return a copy of the input, unchanged */ + memcpy(out, hs, VARSIZE(hs)); + HS_FIXSIZE(out, hs_count); + HS_SETCOUNT(out, hs_count); + PG_RETURN_POINTER(out); } + /* + * this is in effect a merge between hs and hs2, both of + * which are already sorted by (keylen,key); we take keys from + * hs only; for equal keys, we take the value from hs unless the + * values are equal + */ + + for (i = j = 0; i < hs_count; ) + { + int difference; + + if (j >= hs2_count) + difference = -1; + else + { + int skeylen = HS_KEYLEN(es,i); + int s2keylen = HS_KEYLEN(es2,j); + if (skeylen == s2keylen) + difference = strncmp(HS_KEY(es,ps,i), + HS_KEY(es2,ps2,j), + skeylen); + else + difference = (skeylen > s2keylen) ? 1 : -1; + } + + if (difference > 0) + ++j; + else if (difference == 0) + { + int svallen = HS_VALLEN(es,i); + int snullval = HS_VALISNULL(es,i); + if (snullval != HS_VALISNULL(es2,j) + || (!snullval + && (svallen != HS_VALLEN(es2,j) + || strncmp(HS_VAL(es,ps,i), HS_VAL(es2,ps2,j), svallen) != 0))) + { + HS_COPYITEM(ed, bufd, pd, + HS_KEY(es,ps,i), HS_KEYLEN(es,i), + svallen, snullval); + ++outcount; + } + ++i, ++j; + } + else + { + HS_COPYITEM(ed, bufd, pd, + HS_KEY(es,ps,i), HS_KEYLEN(es,i), + HS_VALLEN(es,i), HS_VALISNULL(es,i)); + ++outcount; + ++i; + } + } - PG_FREE_IF_COPY(hs, 0); - PG_FREE_IF_COPY(key, 1); + HS_FINALIZE(out,outcount,bufd,pd); PG_RETURN_POINTER(out); } -PG_FUNCTION_INFO_V1(hs_concat); -Datum hs_concat(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_concat); +Datum hstore_concat(PG_FUNCTION_ARGS); Datum -hs_concat(PG_FUNCTION_ARGS) +hstore_concat(PG_FUNCTION_ARGS) { HStore *s1 = PG_GETARG_HS(0); HStore *s2 = PG_GETARG_HS(1); HStore *out = palloc(VARSIZE(s1) + VARSIZE(s2)); char *ps1, *ps2, + *bufd, *pd; HEntry *es1, *es2, *ed; + int s1idx; + int s2idx; + int s1count = HS_COUNT(s1); + int s2count = HS_COUNT(s2); + int outcount = 0; - SET_VARSIZE(out, VARSIZE(s1) + VARSIZE(s2)); - out->size = s1->size + s2->size; + SET_VARSIZE(out, VARSIZE(s1) + VARSIZE(s2) - HSHRDSIZE); + HS_SETCOUNT(out, s1count + s2count); + + if (s1count == 0) + { + /* return a copy of the input, unchanged */ + memcpy(out, s2, VARSIZE(s2)); + HS_FIXSIZE(out, s2count); + HS_SETCOUNT(out, s2count); + PG_RETURN_POINTER(out); + } + + if (s2count == 0) + { + /* return a copy of the input, unchanged */ + memcpy(out, s1, VARSIZE(s1)); + HS_FIXSIZE(out, s1count); + HS_SETCOUNT(out, s1count); + PG_RETURN_POINTER(out); + } ps1 = STRPTR(s1); ps2 = STRPTR(s2); - pd = STRPTR(out); + bufd = pd = STRPTR(out); es1 = ARRPTR(s1); es2 = ARRPTR(s2); ed = ARRPTR(out); - while (es1 - ARRPTR(s1) < s1->size && es2 - ARRPTR(s2) < s2->size) - { - int difference; + /* + * this is in effect a merge between s1 and s2, both of which + * are already sorted by (keylen,key); we take s2 for equal keys + */ - if (es1->keylen == es2->keylen) - difference = strncmp(ps1, ps2, es1->keylen); + for (s1idx = s2idx = 0; s1idx < s1count || s2idx < s2count; ++outcount) + { + int difference; + + if (s1idx >= s1count) + difference = 1; + else if (s2idx >= s2count) + difference = -1; else - difference = (es1->keylen > es2->keylen) ? 1 : -1; - - if (difference == 0) { - memcpy(ed, es2, sizeof(HEntry)); - memcpy(pd, ps2, es2->keylen + ((es2->valisnull) ? 0 : es2->vallen)); - ed->pos = pd - STRPTR(out); - pd += es2->keylen + ((es2->valisnull) ? 0 : es2->vallen); - ed++; - - ps1 += es1->keylen + ((es1->valisnull) ? 0 : es1->vallen); - es1++; - ps2 += es2->keylen + ((es2->valisnull) ? 0 : es2->vallen); - es2++; + int s1keylen = HS_KEYLEN(es1,s1idx); + int s2keylen = HS_KEYLEN(es2,s2idx); + if (s1keylen == s2keylen) + difference = strncmp(HS_KEY(es1,ps1,s1idx), + HS_KEY(es2,ps2,s2idx), + s1keylen); + else + difference = (s1keylen > s2keylen) ? 1 : -1; } - else if (difference > 0) + + if (difference >= 0) { - memcpy(ed, es2, sizeof(HEntry)); - memcpy(pd, ps2, es2->keylen + ((es2->valisnull) ? 0 : es2->vallen)); - ed->pos = pd - STRPTR(out); - pd += es2->keylen + ((es2->valisnull) ? 0 : es2->vallen); - ed++; - - ps2 += es2->keylen + ((es2->valisnull) ? 0 : es2->vallen); - es2++; + HS_COPYITEM(ed, bufd, pd, + HS_KEY(es2,ps2,s2idx), HS_KEYLEN(es2,s2idx), + HS_VALLEN(es2,s2idx), HS_VALISNULL(es2,s2idx)); + ++s2idx; + if (difference == 0) + ++s1idx; } else { - memcpy(ed, es1, sizeof(HEntry)); - memcpy(pd, ps1, es1->keylen + ((es1->valisnull) ? 0 : es1->vallen)); - ed->pos = pd - STRPTR(out); - pd += es1->keylen + ((es1->valisnull) ? 0 : es1->vallen); - ed++; - - ps1 += es1->keylen + ((es1->valisnull) ? 0 : es1->vallen); - es1++; + HS_COPYITEM(ed, bufd, pd, + HS_KEY(es1,ps1,s1idx), HS_KEYLEN(es1,s1idx), + HS_VALLEN(es1,s1idx), HS_VALISNULL(es1,s1idx)); + ++s1idx; } } - while (es1 - ARRPTR(s1) < s1->size) - { - memcpy(ed, es1, sizeof(HEntry)); - memcpy(pd, ps1, es1->keylen + ((es1->valisnull) ? 0 : es1->vallen)); - ed->pos = pd - STRPTR(out); - pd += es1->keylen + ((es1->valisnull) ? 0 : es1->vallen); - ed++; + HS_FINALIZE(out,outcount,bufd,pd); - ps1 += es1->keylen + ((es1->valisnull) ? 0 : es1->vallen); - es1++; - } + PG_RETURN_POINTER(out); +} - while (es2 - ARRPTR(s2) < s2->size) - { - memcpy(ed, es2, sizeof(HEntry)); - memcpy(pd, ps2, es2->keylen + ((es2->valisnull) ? 0 : es2->vallen)); - ed->pos = pd - STRPTR(out); - pd += es2->keylen + ((es2->valisnull) ? 0 : es2->vallen); - ed++; - ps2 += es2->keylen + ((es2->valisnull) ? 0 : es2->vallen); - es2++; +PG_FUNCTION_INFO_V1(hstore_slice_to_array); +Datum hstore_slice_to_array(PG_FUNCTION_ARGS); +Datum +hstore_slice_to_array(PG_FUNCTION_ARGS) +{ + HStore *hs = PG_GETARG_HS(0); + HEntry *entries = ARRPTR(hs); + char *ptr = STRPTR(hs); + ArrayType *key_array = PG_GETARG_ARRAYTYPE_P(1); + ArrayType *aout; + Datum *key_datums; + bool *key_nulls; + Datum *out_datums; + bool *out_nulls; + int key_count; + int i; + + deconstruct_array(key_array, + TEXTOID, -1, false, 'i', + &key_datums, &key_nulls, &key_count); + + if (key_count == 0) + { + aout = construct_empty_array(TEXTOID); + PG_RETURN_POINTER(aout); } - if (ed - ARRPTR(out) != out->size) - { - int buflen = pd - STRPTR(out); + out_datums = palloc(sizeof(Datum) * key_count); + out_nulls = palloc(sizeof(bool) * key_count); - pd = STRPTR(out); + for (i = 0; i < key_count; ++i) + { + text *key = (text*) DatumGetPointer(key_datums[i]); + int idx; - out->size = ed - ARRPTR(out); + if (key_nulls[i]) + idx = -1; + else + idx = hstoreFindKey(hs, NULL, VARDATA(key), VARSIZE(key) - VARHDRSZ); - memmove(STRPTR(out), pd, buflen); - SET_VARSIZE(out, CALCDATASIZE(out->size, buflen)); + if (idx < 0 || HS_VALISNULL(entries,idx)) + { + out_nulls[i] = true; + out_datums[i] = (Datum) 0; + } + else + { + out_datums[i] = PointerGetDatum( + cstring_to_text_with_len(HS_VAL(entries,ptr,idx), + HS_VALLEN(entries,idx))); + out_nulls[i] = false; + } } - PG_FREE_IF_COPY(s1, 0); - PG_FREE_IF_COPY(s2, 1); + aout = construct_md_array(out_datums, out_nulls, + ARR_NDIM(key_array), + ARR_DIMS(key_array), + ARR_LBOUND(key_array), + TEXTOID, -1, false, 'i'); - PG_RETURN_POINTER(out); + PG_RETURN_POINTER(aout); } -PG_FUNCTION_INFO_V1(tconvert); -Datum tconvert(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_slice_to_hstore); +Datum hstore_slice_to_hstore(PG_FUNCTION_ARGS); Datum -tconvert(PG_FUNCTION_ARGS) +hstore_slice_to_hstore(PG_FUNCTION_ARGS) { - text *key; - text *val = NULL; - int len; - HStore *out; + HStore *hs = PG_GETARG_HS(0); + HEntry *entries = ARRPTR(hs); + char *ptr = STRPTR(hs); + ArrayType *key_array = PG_GETARG_ARRAYTYPE_P(1); + HStore *out; + int nkeys; + Pairs *key_pairs = hstoreArrayToPairs(key_array, &nkeys); + Pairs *out_pairs; + int bufsiz; + int lastidx = 0; + int i; + int out_count = 0; + + if (nkeys == 0) + { + out = hstorePairs(NULL, 0, 0); + PG_RETURN_POINTER(out); + } - if (PG_ARGISNULL(0)) - PG_RETURN_NULL(); + out_pairs = palloc(sizeof(Pairs) * nkeys); + bufsiz = 0; - key = PG_GETARG_TEXT_P(0); + /* + * we exploit the fact that the pairs list is already sorted into + * strictly increasing order to narrow the hstoreFindKey search; + * each search can start one entry past the previous "found" + * entry, or at the lower bound of the last search. + */ - if (PG_ARGISNULL(1)) - len = CALCDATASIZE(1, VARSIZE(key)); - else + for (i = 0; i < nkeys; ++i) { - val = PG_GETARG_TEXT_P(1); - len = CALCDATASIZE(1, VARSIZE(key) + VARSIZE(val) - 2 * VARHDRSZ); - } - out = palloc(len); - SET_VARSIZE(out, len); - out->size = 1; + int idx = hstoreFindKey(hs, &lastidx, + key_pairs[i].key, key_pairs[i].keylen); - ARRPTR(out)->keylen = hstoreCheckKeyLen(VARSIZE(key) - VARHDRSZ); - if (PG_ARGISNULL(1)) - { - ARRPTR(out)->vallen = 0; - ARRPTR(out)->valisnull = true; - } - else - { - ARRPTR(out)->vallen = hstoreCheckValLen(VARSIZE(val) - VARHDRSZ); - ARRPTR(out)->valisnull = false; + if (idx >= 0) + { + out_pairs[out_count].key = key_pairs[i].key; + bufsiz += (out_pairs[out_count].keylen = key_pairs[i].keylen); + out_pairs[out_count].val = HS_VAL(entries,ptr,idx); + bufsiz += (out_pairs[out_count].vallen = HS_VALLEN(entries,idx)); + out_pairs[out_count].isnull = HS_VALISNULL(entries,idx); + out_pairs[out_count].needfree = false; + ++out_count; + } } - ARRPTR(out)->pos = 0; - memcpy(STRPTR(out), VARDATA(key), ARRPTR(out)->keylen); - if (!PG_ARGISNULL(1)) - { - memcpy(STRPTR(out) + ARRPTR(out)->keylen, VARDATA(val), ARRPTR(out)->vallen); - PG_FREE_IF_COPY(val, 1); - } + /* + * we don't use uniquePairs here because we know that the + * pairs list is already sorted and uniq'ed. + */ - PG_FREE_IF_COPY(key, 0); + out = hstorePairs(out_pairs, out_count, bufsiz); PG_RETURN_POINTER(out); } -PG_FUNCTION_INFO_V1(akeys); -Datum akeys(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_akeys); +Datum hstore_akeys(PG_FUNCTION_ARGS); Datum -akeys(PG_FUNCTION_ARGS) +hstore_akeys(PG_FUNCTION_ARGS) { HStore *hs = PG_GETARG_HS(0); Datum *d; ArrayType *a; - HEntry *ptr = ARRPTR(hs); + HEntry *entries = ARRPTR(hs); char *base = STRPTR(hs); + int count = HS_COUNT(hs); + int i; - d = (Datum *) palloc(sizeof(Datum) * (hs->size + 1)); - while (ptr - ARRPTR(hs) < hs->size) + if (count == 0) { - text *item; - - item = cstring_to_text_with_len(base + ptr->pos, ptr->keylen); - d[ptr - ARRPTR(hs)] = PointerGetDatum(item); - ptr++; + a = construct_empty_array(TEXTOID); + PG_RETURN_POINTER(a); } - a = construct_array(d, - hs->size, - TEXTOID, - -1, - false, - 'i'); + d = (Datum *) palloc(sizeof(Datum) * count); - ptr = ARRPTR(hs); - while (ptr - ARRPTR(hs) < hs->size) + for (i = 0; i < count; ++i) { - pfree(DatumGetPointer(d[ptr - ARRPTR(hs)])); - ptr++; + text *item = cstring_to_text_with_len(HS_KEY(entries,base,i), + HS_KEYLEN(entries,i)); + d[i] = PointerGetDatum(item); } - pfree(d); - PG_FREE_IF_COPY(hs, 0); + a = construct_array(d, count, + TEXTOID, -1, false, 'i'); PG_RETURN_POINTER(a); } -PG_FUNCTION_INFO_V1(avals); -Datum avals(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_avals); +Datum hstore_avals(PG_FUNCTION_ARGS); Datum -avals(PG_FUNCTION_ARGS) +hstore_avals(PG_FUNCTION_ARGS) { HStore *hs = PG_GETARG_HS(0); Datum *d; + bool *nulls; ArrayType *a; - HEntry *ptr = ARRPTR(hs); + HEntry *entries = ARRPTR(hs); char *base = STRPTR(hs); + int count = HS_COUNT(hs); + int lb = 1; + int i; - d = (Datum *) palloc(sizeof(Datum) * (hs->size + 1)); - while (ptr - ARRPTR(hs) < hs->size) + if (count == 0) { - text *item; - - item = cstring_to_text_with_len(base + ptr->pos + ptr->keylen, - (ptr->valisnull) ? 0 : ptr->vallen); - d[ptr - ARRPTR(hs)] = PointerGetDatum(item); - ptr++; + a = construct_empty_array(TEXTOID); + PG_RETURN_POINTER(a); } - a = construct_array(d, - hs->size, - TEXTOID, - -1, - false, - 'i'); + d = (Datum *) palloc(sizeof(Datum) * count); + nulls = (bool *) palloc(sizeof(bool) * count); - ptr = ARRPTR(hs); - while (ptr - ARRPTR(hs) < hs->size) + for (i = 0; i < count; ++i) { - pfree(DatumGetPointer(d[ptr - ARRPTR(hs)])); - ptr++; + if (HS_VALISNULL(entries,i)) + { + d[i] = (Datum) 0; + nulls[i] = true; + } + else + { + text *item = cstring_to_text_with_len(HS_VAL(entries,base,i), + HS_VALLEN(entries,i)); + d[i] = PointerGetDatum(item); + nulls[i] = false; + } } - pfree(d); - PG_FREE_IF_COPY(hs, 0); + a = construct_md_array(d, nulls, 1, &count, &lb, + TEXTOID, -1, false, 'i'); PG_RETURN_POINTER(a); } -typedef struct + +static ArrayType * +hstore_to_array_internal(HStore *hs, int ndims) +{ + HEntry *entries = ARRPTR(hs); + char *base = STRPTR(hs); + int count = HS_COUNT(hs); + int out_size[2] = { 0, 2 }; + int lb[2] = { 1, 1 }; + Datum *out_datums; + bool *out_nulls; + int i; + + Assert(ndims < 3); + + if (count == 0 || ndims == 0) + return construct_empty_array(TEXTOID); + + out_size[0] = count * 2 / ndims; + out_datums = palloc(sizeof(Datum) * count * 2); + out_nulls = palloc(sizeof(bool) * count * 2); + + for (i = 0; i < count; ++i) + { + text *key = cstring_to_text_with_len(HS_KEY(entries,base,i), + HS_KEYLEN(entries,i)); + out_datums[i*2] = PointerGetDatum(key); + out_nulls[i*2] = false; + + if (HS_VALISNULL(entries,i)) + { + out_datums[i*2+1] = (Datum) 0; + out_nulls[i*2+1] = true; + } + else + { + text *item = cstring_to_text_with_len(HS_VAL(entries,base,i), + HS_VALLEN(entries,i)); + out_datums[i*2+1] = PointerGetDatum(item); + out_nulls[i*2+1] = false; + } + } + + return construct_md_array(out_datums, out_nulls, + ndims, out_size, lb, + TEXTOID, -1, false, 'i'); +} + +PG_FUNCTION_INFO_V1(hstore_to_array); +Datum hstore_to_array(PG_FUNCTION_ARGS); +Datum +hstore_to_array(PG_FUNCTION_ARGS) +{ + HStore *hs = PG_GETARG_HS(0); + ArrayType *out = hstore_to_array_internal(hs, 1); + + PG_RETURN_POINTER(out); +} + +PG_FUNCTION_INFO_V1(hstore_to_matrix); +Datum hstore_to_matrix(PG_FUNCTION_ARGS); +Datum +hstore_to_matrix(PG_FUNCTION_ARGS) { - HStore *hs; - int i; -} AKStore; + HStore *hs = PG_GETARG_HS(0); + ArrayType *out = hstore_to_array_internal(hs, 2); + + PG_RETURN_POINTER(out); +} + +/* + * Common initialization function for the various set-returning + * funcs. fcinfo is only passed if the function is to return a + * composite; it will be used to look up the return tupledesc. + * we stash a copy of the hstore in the multi-call context in + * case it was originally toasted. (At least I assume that's why; + * there was no explanatory comment in the original code. --AG) + */ static void -setup_firstcall(FuncCallContext *funcctx, HStore *hs) +setup_firstcall(FuncCallContext *funcctx, HStore * hs, + FunctionCallInfoData *fcinfo) { MemoryContext oldcontext; - AKStore *st; + HStore *st; oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); - st = (AKStore *) palloc(sizeof(AKStore)); - st->i = 0; - st->hs = (HStore *) palloc(VARSIZE(hs)); - memcpy(st->hs, hs, VARSIZE(hs)); + st = (HStore *) palloc(VARSIZE(hs)); + memcpy(st, hs, VARSIZE(hs)); funcctx->user_fctx = (void *) st; + + if (fcinfo) + { + TupleDesc tupdesc; + + /* Build a tuple descriptor for our result type */ + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + funcctx->tuple_desc = BlessTupleDesc(tupdesc); + } + MemoryContextSwitchTo(oldcontext); } -PG_FUNCTION_INFO_V1(skeys); -Datum skeys(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_skeys); +Datum hstore_skeys(PG_FUNCTION_ARGS); Datum -skeys(PG_FUNCTION_ARGS) +hstore_skeys(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; - AKStore *st; + HStore *hs; + int i; if (SRF_IS_FIRSTCALL()) { - HStore *hs = PG_GETARG_HS(0); - + hs = PG_GETARG_HS(0); funcctx = SRF_FIRSTCALL_INIT(); - setup_firstcall(funcctx, hs); - PG_FREE_IF_COPY(hs, 0); + setup_firstcall(funcctx, hs, NULL); } funcctx = SRF_PERCALL_SETUP(); - st = (AKStore *) funcctx->user_fctx; + hs = (HStore *) funcctx->user_fctx; + i = funcctx->call_cntr; - if (st->i < st->hs->size) + if (i < HS_COUNT(hs)) { - HEntry *ptr = &(ARRPTR(st->hs)[st->i]); + HEntry *entries = ARRPTR(hs); text *item; - item = cstring_to_text_with_len(STRPTR(st->hs) + ptr->pos, - ptr->keylen); - st->i++; + item = cstring_to_text_with_len(HS_KEY(entries,STRPTR(hs),i), + HS_KEYLEN(entries,i)); SRF_RETURN_NEXT(funcctx, PointerGetDatum(item)); } - pfree(st->hs); - pfree(st); - SRF_RETURN_DONE(funcctx); } -PG_FUNCTION_INFO_V1(svals); -Datum svals(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_svals); +Datum hstore_svals(PG_FUNCTION_ARGS); Datum -svals(PG_FUNCTION_ARGS) +hstore_svals(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; - AKStore *st; + HStore *hs; + int i; if (SRF_IS_FIRSTCALL()) { - HStore *hs = PG_GETARG_HS(0); - + hs = PG_GETARG_HS(0); funcctx = SRF_FIRSTCALL_INIT(); - setup_firstcall(funcctx, hs); - PG_FREE_IF_COPY(hs, 0); + setup_firstcall(funcctx, hs, NULL); } funcctx = SRF_PERCALL_SETUP(); - st = (AKStore *) funcctx->user_fctx; + hs = (HStore *) funcctx->user_fctx; + i = funcctx->call_cntr; - if (st->i < st->hs->size) + if (i < HS_COUNT(hs)) { - HEntry *ptr = &(ARRPTR(st->hs)[st->i]); + HEntry *entries = ARRPTR(hs); - if (ptr->valisnull) + if (HS_VALISNULL(entries,i)) { ReturnSetInfo *rsi; - st->i++; + /* ugly ugly ugly. why no macro for this? */ (funcctx)->call_cntr++; rsi = (ReturnSetInfo *) fcinfo->resultinfo; rsi->isDone = ExprMultipleResult; @@ -502,144 +939,306 @@ svals(PG_FUNCTION_ARGS) { text *item; - item = cstring_to_text_with_len(STRPTR(st->hs) + ptr->pos + ptr->keylen, - ptr->vallen); - st->i++; + item = cstring_to_text_with_len(HS_VAL(entries,STRPTR(hs),i), + HS_VALLEN(entries,i)); SRF_RETURN_NEXT(funcctx, PointerGetDatum(item)); } } - pfree(st->hs); - pfree(st); - SRF_RETURN_DONE(funcctx); } -PG_FUNCTION_INFO_V1(hs_contains); -Datum hs_contains(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_contains); +Datum hstore_contains(PG_FUNCTION_ARGS); Datum -hs_contains(PG_FUNCTION_ARGS) +hstore_contains(PG_FUNCTION_ARGS) { HStore *val = PG_GETARG_HS(0); HStore *tmpl = PG_GETARG_HS(1); bool res = true; HEntry *te = ARRPTR(tmpl); - char *vv = STRPTR(val); - char *tv = STRPTR(tmpl); - - while (res && te - ARRPTR(tmpl) < tmpl->size) + char *tstr = STRPTR(tmpl); + HEntry *ve = ARRPTR(val); + char *vstr = STRPTR(val); + int tcount = HS_COUNT(tmpl); + int lastidx = 0; + int i; + + /* + * we exploit the fact that keys in "tmpl" are in strictly + * increasing order to narrow the hstoreFindKey search; each search + * can start one entry past the previous "found" entry, or at the + * lower bound of the search + */ + + for (i = 0; res && i < tcount; ++i) { - HEntry *entry = findkey(val, tv + te->pos, te->keylen); + int idx = hstoreFindKey(val, &lastidx, + HS_KEY(te,tstr,i), HS_KEYLEN(te,i)); - if (entry) + if (idx >= 0) { - if (te->valisnull || entry->valisnull) - { - if (!(te->valisnull && entry->valisnull)) - res = false; - } - else if (te->vallen != entry->vallen || - strncmp(vv + entry->pos + entry->keylen, - tv + te->pos + te->keylen, - te->vallen)) + bool nullval = HS_VALISNULL(te,i); + int vallen = HS_VALLEN(te,i); + + if (nullval != HS_VALISNULL(ve,idx) + || (!nullval + && (vallen != HS_VALLEN(ve,idx) + || strncmp(HS_VAL(te,tstr,i), HS_VAL(ve,vstr,idx), vallen)))) res = false; } else res = false; - te++; } - PG_FREE_IF_COPY(val, 0); - PG_FREE_IF_COPY(tmpl, 1); - PG_RETURN_BOOL(res); } -PG_FUNCTION_INFO_V1(hs_contained); -Datum hs_contained(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_contained); +Datum hstore_contained(PG_FUNCTION_ARGS); Datum -hs_contained(PG_FUNCTION_ARGS) +hstore_contained(PG_FUNCTION_ARGS) { - PG_RETURN_DATUM(DirectFunctionCall2( - hs_contains, + PG_RETURN_DATUM(DirectFunctionCall2(hstore_contains, PG_GETARG_DATUM(1), PG_GETARG_DATUM(0) )); } -PG_FUNCTION_INFO_V1(each); -Datum each(PG_FUNCTION_ARGS); + +PG_FUNCTION_INFO_V1(hstore_each); +Datum hstore_each(PG_FUNCTION_ARGS); Datum -each(PG_FUNCTION_ARGS) +hstore_each(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; - AKStore *st; + HStore *hs; + int i; if (SRF_IS_FIRSTCALL()) { - TupleDesc tupdesc; - MemoryContext oldcontext; - HStore *hs = PG_GETARG_HS(0); - + hs = PG_GETARG_HS(0); funcctx = SRF_FIRSTCALL_INIT(); - oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); - st = (AKStore *) palloc(sizeof(AKStore)); - st->i = 0; - st->hs = (HStore *) palloc(VARSIZE(hs)); - memcpy(st->hs, hs, VARSIZE(hs)); - funcctx->user_fctx = (void *) st; - - /* Build a tuple descriptor for our result type */ - if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) - elog(ERROR, "return type must be a row type"); - - funcctx->attinmeta = TupleDescGetAttInMetadata(tupdesc); - - MemoryContextSwitchTo(oldcontext); - PG_FREE_IF_COPY(hs, 0); + setup_firstcall(funcctx, hs, fcinfo); } funcctx = SRF_PERCALL_SETUP(); - st = (AKStore *) funcctx->user_fctx; + hs = (HStore *) funcctx->user_fctx; + i = funcctx->call_cntr; - if (st->i < st->hs->size) + if (i < HS_COUNT(hs)) { - HEntry *ptr = &(ARRPTR(st->hs)[st->i]); + HEntry *entries = ARRPTR(hs); + char *ptr = STRPTR(hs); Datum res, dvalues[2]; bool nulls[2] = {false, false}; text *item; HeapTuple tuple; - item = cstring_to_text_with_len(STRPTR(st->hs) + ptr->pos, ptr->keylen); + item = cstring_to_text_with_len(HS_KEY(entries,ptr,i), + HS_KEYLEN(entries,i)); dvalues[0] = PointerGetDatum(item); - if (ptr->valisnull) + if (HS_VALISNULL(entries,i)) { dvalues[1] = (Datum) 0; nulls[1] = true; } else { - item = cstring_to_text_with_len(STRPTR(st->hs) + ptr->pos + ptr->keylen, - ptr->vallen); + item = cstring_to_text_with_len(HS_VAL(entries,ptr,i), + HS_VALLEN(entries,i)); dvalues[1] = PointerGetDatum(item); } - st->i++; - tuple = heap_form_tuple(funcctx->attinmeta->tupdesc, dvalues, nulls); + tuple = heap_form_tuple(funcctx->tuple_desc, dvalues, nulls); res = HeapTupleGetDatum(tuple); - pfree(DatumGetPointer(dvalues[0])); - if (!nulls[1]) - pfree(DatumGetPointer(dvalues[1])); - SRF_RETURN_NEXT(funcctx, PointerGetDatum(res)); } - pfree(st->hs); - pfree(st); - SRF_RETURN_DONE(funcctx); } + + +/* + * btree sort order for hstores isn't intended to be useful; we really only + * care about equality versus non-equality. we compare the entire string + * buffer first, then the entry pos array. + */ + +PG_FUNCTION_INFO_V1(hstore_cmp); +Datum hstore_cmp(PG_FUNCTION_ARGS); +Datum +hstore_cmp(PG_FUNCTION_ARGS) +{ + HStore *hs1 = PG_GETARG_HS(0); + HStore *hs2 = PG_GETARG_HS(1); + int hcount1 = HS_COUNT(hs1); + int hcount2 = HS_COUNT(hs2); + int res = 0; + + if (hcount1 == 0 || hcount2 == 0) + { + /* + * if either operand is empty, and the other is nonempty, the + * nonempty one is larger. If both are empty they are equal. + */ + if (hcount1 > 0) + res = 1; + else if (hcount2 > 0) + res = -1; + } + else + { + /* here we know both operands are nonempty */ + char *str1 = STRPTR(hs1); + char *str2 = STRPTR(hs2); + HEntry *ent1 = ARRPTR(hs1); + HEntry *ent2 = ARRPTR(hs2); + size_t len1 = HSE_ENDPOS(ent1[2*hcount1 - 1]); + size_t len2 = HSE_ENDPOS(ent2[2*hcount2 - 1]); + + res = memcmp(str1, str2, Min(len1,len2)); + + if (res == 0) + { + if (len1 > len2) + res = 1; + else if (len1 < len2) + res = -1; + else if (hcount1 > hcount2) + res = 1; + else if (hcount2 > hcount1) + res = -1; + else + { + int count = hcount1 * 2; + int i; + + for (i = 0; i < count; ++i) + if (HSE_ENDPOS(ent1[i]) != HSE_ENDPOS(ent2[i]) || + HSE_ISNULL(ent1[i]) != HSE_ISNULL(ent2[i])) + break; + if (i < count) + { + if (HSE_ENDPOS(ent1[i]) < HSE_ENDPOS(ent2[i])) + res = -1; + else if (HSE_ENDPOS(ent1[i]) > HSE_ENDPOS(ent2[i])) + res = 1; + else if (HSE_ISNULL(ent1[i])) + res = 1; + else if (HSE_ISNULL(ent2[i])) + res = -1; + } + } + } + else + { + res = (res > 0) ? 1 : -1; + } + } + + /* + * this is a btree support function; this is one of the few + * places where memory needs to be explicitly freed. + */ + PG_FREE_IF_COPY(hs1,0); + PG_FREE_IF_COPY(hs2,1); + PG_RETURN_INT32(res); +} + + +PG_FUNCTION_INFO_V1(hstore_eq); +Datum hstore_eq(PG_FUNCTION_ARGS); +Datum +hstore_eq(PG_FUNCTION_ARGS) +{ + int res = DatumGetInt32(DirectFunctionCall2(hstore_cmp, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1))); + PG_RETURN_BOOL(res == 0); +} + +PG_FUNCTION_INFO_V1(hstore_ne); +Datum hstore_ne(PG_FUNCTION_ARGS); +Datum +hstore_ne(PG_FUNCTION_ARGS) +{ + int res = DatumGetInt32(DirectFunctionCall2(hstore_cmp, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1))); + PG_RETURN_BOOL(res != 0); +} + +PG_FUNCTION_INFO_V1(hstore_gt); +Datum hstore_gt(PG_FUNCTION_ARGS); +Datum +hstore_gt(PG_FUNCTION_ARGS) +{ + int res = DatumGetInt32(DirectFunctionCall2(hstore_cmp, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1))); + PG_RETURN_BOOL(res > 0); +} + +PG_FUNCTION_INFO_V1(hstore_ge); +Datum hstore_ge(PG_FUNCTION_ARGS); +Datum +hstore_ge(PG_FUNCTION_ARGS) +{ + int res = DatumGetInt32(DirectFunctionCall2(hstore_cmp, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1))); + PG_RETURN_BOOL(res >= 0); +} + +PG_FUNCTION_INFO_V1(hstore_lt); +Datum hstore_lt(PG_FUNCTION_ARGS); +Datum +hstore_lt(PG_FUNCTION_ARGS) +{ + int res = DatumGetInt32(DirectFunctionCall2(hstore_cmp, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1))); + PG_RETURN_BOOL(res < 0); +} + +PG_FUNCTION_INFO_V1(hstore_le); +Datum hstore_le(PG_FUNCTION_ARGS); +Datum +hstore_le(PG_FUNCTION_ARGS) +{ + int res = DatumGetInt32(DirectFunctionCall2(hstore_cmp, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1))); + PG_RETURN_BOOL(res <= 0); +} + + +PG_FUNCTION_INFO_V1(hstore_hash); +Datum hstore_hash(PG_FUNCTION_ARGS); +Datum +hstore_hash(PG_FUNCTION_ARGS) +{ + HStore *hs = PG_GETARG_HS(0); + Datum hval = hash_any((unsigned char *)VARDATA(hs), + VARSIZE(hs) - VARHDRSZ); + + /* + * this is the only place in the code that cares whether the + * overall varlena size exactly matches the true data size; + * this assertion should be maintained by all the other code, + * but we make it explicit here. + */ + Assert(VARSIZE(hs) == + CALCDATASIZE(HS_COUNT(hs), + HSE_ENDPOS(ARRPTR(hs)[2*HS_COUNT(hs) - 1]))); + + PG_FREE_IF_COPY(hs,0); + PG_RETURN_DATUM(hval); +} diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql index 9da6cd13df..a88ff1dab9 100644 --- a/contrib/hstore/sql/hstore.sql +++ b/contrib/hstore/sql/hstore.sql @@ -65,6 +65,13 @@ select ('aa=>b, c=>d , b=>16'::hstore->'gg') is null; select ('aa=>NULL, c=>d , b=>16'::hstore->'aa') is null; select ('aa=>"NULL", c=>d , b=>16'::hstore->'aa') is null; +-- -> array operator + +select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['aa','c']; +select 'aa=>"NULL", c=>d , b=>16'::hstore -> ARRAY['c','aa']; +select 'aa=>NULL, c=>d , b=>16'::hstore -> ARRAY['aa','c',null]; +select 'aa=>1, c=>3, b=>2, d=>4'::hstore -> ARRAY[['b','d'],['aa','c']]; + -- exists/defined select exist('a=>NULL, b=>qq', 'a'); @@ -75,6 +82,20 @@ select defined('a=>NULL, b=>qq', 'a'); select defined('a=>NULL, b=>qq', 'b'); select defined('a=>NULL, b=>qq', 'c'); select defined('a=>"NULL", b=>qq', 'a'); +select hstore 'a=>NULL, b=>qq' ? 'a'; +select hstore 'a=>NULL, b=>qq' ? 'b'; +select hstore 'a=>NULL, b=>qq' ? 'c'; +select hstore 'a=>"NULL", b=>qq' ? 'a'; +select hstore 'a=>NULL, b=>qq' ?| ARRAY['a','b']; +select hstore 'a=>NULL, b=>qq' ?| ARRAY['b','a']; +select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','a']; +select hstore 'a=>NULL, b=>qq' ?| ARRAY['c','d']; +select hstore 'a=>NULL, b=>qq' ?| '{}'::text[]; +select hstore 'a=>NULL, b=>qq' ?& ARRAY['a','b']; +select hstore 'a=>NULL, b=>qq' ?& ARRAY['b','a']; +select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','a']; +select hstore 'a=>NULL, b=>qq' ?& ARRAY['c','d']; +select hstore 'a=>NULL, b=>qq' ?& '{}'::text[]; -- delete @@ -83,6 +104,47 @@ 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 'a=>1 , b=>2, c=>3'::hstore - 'a'::text; +select 'a=>null , b=>2, c=>3'::hstore - 'a'::text; +select 'a=>1 , b=>2, c=>3'::hstore - 'b'::text; +select 'a=>1 , b=>2, c=>3'::hstore - 'c'::text; +select 'a=>1 , b=>2, c=>3'::hstore - 'd'::text; +select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b'::text) + = pg_column_size('a=>1, b=>2'::hstore); + +-- delete (array) + +select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','e']); +select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['d','b']); +select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY['a','c']); +select delete('a=>1 , b=>2, c=>3'::hstore, ARRAY[['b'],['c'],['a']]); +select delete('a=>1 , b=>2, c=>3'::hstore, '{}'::text[]); +select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','e']; +select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['d','b']; +select 'a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c']; +select 'a=>1 , b=>2, c=>3'::hstore - ARRAY[['b'],['c'],['a']]; +select 'a=>1 , b=>2, c=>3'::hstore - '{}'::text[]; +select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ARRAY['a','c']) + = pg_column_size('b=>2'::hstore); +select pg_column_size('a=>1 , b=>2, c=>3'::hstore - '{}'::text[]) + = pg_column_size('a=>1, b=>2, c=>3'::hstore); + +-- delete (hstore) + +select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>4, b=>2'::hstore); +select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>NULL, c=>3'::hstore); +select delete('aa=>1 , b=>2, c=>3'::hstore, 'aa=>1, b=>2, c=>3'::hstore); +select delete('aa=>1 , b=>2, c=>3'::hstore, 'b=>2'::hstore); +select delete('aa=>1 , b=>2, c=>3'::hstore, ''::hstore); +select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>4, b=>2'::hstore; +select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>NULL, c=>3'::hstore; +select 'aa=>1 , b=>2, c=>3'::hstore - 'aa=>1, b=>2, c=>3'::hstore; +select 'aa=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore; +select 'aa=>1 , b=>2, c=>3'::hstore - ''::hstore; +select pg_column_size('a=>1 , b=>2, c=>3'::hstore - 'b=>2'::hstore) + = pg_column_size('a=>1, c=>3'::hstore); +select pg_column_size('a=>1 , b=>2, c=>3'::hstore - ''::hstore) + = pg_column_size('a=>1, b=>2, c=>3'::hstore); -- || select 'aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'; @@ -90,12 +152,104 @@ 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 pg_column_size(''::hstore || ''::hstore) = pg_column_size(''::hstore); +select pg_column_size('aa=>1'::hstore || 'b=>2'::hstore) + = pg_column_size('aa=>1, b=>2'::hstore); +select pg_column_size('aa=>1, b=>2'::hstore || ''::hstore) + = pg_column_size('aa=>1, b=>2'::hstore); +select pg_column_size(''::hstore || 'aa=>1, b=>2'::hstore) + = pg_column_size('aa=>1, b=>2'::hstore); -- => select 'a=>g, b=>c'::hstore || ( 'asd'=>'gf' ); select 'a=>g, b=>c'::hstore || ( 'b'=>'gf' ); select 'a=>g, b=>c'::hstore || ( 'b'=>'NULL' ); select 'a=>g, b=>c'::hstore || ( 'b'=>NULL ); +select ('a=>g, b=>c'::hstore || ( NULL=>'b' )) is null; +select pg_column_size(('b'=>'gf')) + = pg_column_size('b=>gf'::hstore); +select pg_column_size('a=>g, b=>c'::hstore || ('b'=>'gf')) + = pg_column_size('a=>g, b=>gf'::hstore); + +-- => arrays +select ARRAY['a','b','asd'] => ARRAY['g','h','i']; +select ARRAY['a','b','asd'] => ARRAY['g','h',NULL]; +select ARRAY['z','y','x'] => ARRAY['1','2','3']; +select ARRAY['aaa','bb','c','d'] => ARRAY[null::text,null,null,null]; +select ARRAY['aaa','bb','c','d'] => null; +select hstore 'aa=>1, b=>2, c=>3' => ARRAY['g','h','i']; +select hstore 'aa=>1, b=>2, c=>3' => ARRAY['c','b']; +select hstore 'aa=>1, b=>2, c=>3' => ARRAY['aa','b']; +select hstore 'aa=>1, b=>2, c=>3' => ARRAY['c','b','aa']; +select quote_literal('{}'::text[] => '{}'::text[]); +select quote_literal('{}'::text[] => null); +select ARRAY['a'] => '{}'::text[]; -- error +select '{}'::text[] => ARRAY['a']; -- error +select pg_column_size(ARRAY['a','b','asd'] => ARRAY['g','h','i']) + = pg_column_size('a=>g, b=>h, asd=>i'::hstore); +select pg_column_size(hstore 'aa=>1, b=>2, c=>3' => ARRAY['c','b']) + = pg_column_size('b=>2, c=>3'::hstore); +select pg_column_size(hstore 'aa=>1, b=>2, c=>3' => ARRAY['c','b','aa']) + = pg_column_size('aa=>1, b=>2, c=>3'::hstore); + +-- array input +select '{}'::text[]::hstore; +select ARRAY['a','g','b','h','asd']::hstore; +select ARRAY['a','g','b','h','asd','i']::hstore; +select ARRAY[['a','g'],['b','h'],['asd','i']]::hstore; +select ARRAY[['a','g','b'],['h','asd','i']]::hstore; +select ARRAY[[['a','g'],['b','h'],['asd','i']]]::hstore; +select hstore('{}'::text[]); +select hstore(ARRAY['a','g','b','h','asd']); +select hstore(ARRAY['a','g','b','h','asd','i']); +select hstore(ARRAY[['a','g'],['b','h'],['asd','i']]); +select hstore(ARRAY[['a','g','b'],['h','asd','i']]); +select hstore(ARRAY[[['a','g'],['b','h'],['asd','i']]]); +select hstore('[0:5]={a,g,b,h,asd,i}'::text[]); +select hstore('[0:2][1:2]={{a,g},{b,h},{asd,i}}'::text[]); + +-- records +select hstore(v) from (values (1, 'foo', 1.2, 3::float8)) v(a,b,c,d); +create domain hstestdom1 as integer not null default 0; +create table testhstore0 (a integer, b text, c numeric, d float8); +create table testhstore1 (a integer, b text, c numeric, d float8, e hstestdom1); +insert into testhstore0 values (1, 'foo', 1.2, 3::float8); +insert into testhstore1 values (1, 'foo', 1.2, 3::float8); +select hstore(v) from testhstore1 v; +select hstore(null::testhstore0); +select hstore(null::testhstore1); +select pg_column_size(hstore(v)) + = pg_column_size('a=>1, b=>"foo", c=>"1.2", d=>"3", e=>"0"'::hstore) + from testhstore1 v; +select populate_record(v, ('c' => '3.45')) from testhstore1 v; +select populate_record(v, ('d' => '3.45')) from testhstore1 v; +select populate_record(v, ('e' => '123')) from testhstore1 v; +select populate_record(v, ('e' => null)) from testhstore1 v; +select populate_record(v, ('c' => null)) from testhstore1 v; +select populate_record(v, ('b' => 'foo') || ('a' => '123')) from testhstore1 v; +select populate_record(v, ('b' => 'foo') || ('e' => null)) from testhstore0 v; +select populate_record(v, ('b' => 'foo') || ('e' => null)) from testhstore1 v; +select populate_record(v, '') from testhstore0 v; +select populate_record(v, '') from testhstore1 v; +select populate_record(null::testhstore1, ('c' => '3.45') || ('a' => '123')); +select populate_record(null::testhstore1, ('c' => '3.45') || ('e' => '123')); +select populate_record(null::testhstore0, ''); +select populate_record(null::testhstore1, ''); +select v #= ('c' => '3.45') from testhstore1 v; +select v #= ('d' => '3.45') from testhstore1 v; +select v #= ('e' => '123') from testhstore1 v; +select v #= ('c' => null) from testhstore1 v; +select v #= ('e' => null) from testhstore0 v; +select v #= ('e' => null) from testhstore1 v; +select v #= (('b' => 'foo') || ('a' => '123')) from testhstore1 v; +select v #= (('b' => 'foo') || ('e' => '123')) from testhstore1 v; +select v #= hstore '' from testhstore0 v; +select v #= hstore '' from testhstore1 v; +select null::testhstore1 #= (('c' => '3.45') || ('a' => '123')); +select null::testhstore1 #= (('c' => '3.45') || ('e' => '123')); +select null::testhstore0 #= hstore ''; +select null::testhstore1 #= hstore ''; +select v #= h from testhstore1 v, (values (hstore 'a=>123',1),('b=>foo,c=>3.21',2),('a=>null',3),('e=>123',4),('f=>blah',5)) x(h,i) order by i; -- keys/values select akeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); @@ -106,6 +260,12 @@ select avals('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>NULL'); select avals('""=>1'); select avals(''); +select hstore_to_array('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore); +select %% 'aa=>1, cq=>l, b=>g, fg=>NULL'; + +select hstore_to_matrix('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore); +select %# 'aa=>1, cq=>l, b=>g, fg=>NULL'; + select * from skeys('aa=>1 , b=>2, cq=>3'::hstore || 'cq=>l, b=>g, fg=>f'); select * from skeys('""=>1'); select * from skeys(''); @@ -132,6 +292,8 @@ 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 testhstore where h ? 'public'; +select count(*) from testhstore where h ?| ARRAY['public','disabled']; +select count(*) from testhstore where h ?& ARRAY['public','disabled']; create index hidx on testhstore using gist(h); set enable_seqscan=off; @@ -140,6 +302,8 @@ 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 testhstore where h ? 'public'; +select count(*) from testhstore where h ?| ARRAY['public','disabled']; +select count(*) from testhstore where h ?& ARRAY['public','disabled']; drop index hidx; create index hidx on testhstore using gin (h); @@ -149,6 +313,26 @@ 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 testhstore where h ? 'public'; +select count(*) from testhstore where h ?| ARRAY['public','disabled']; +select count(*) from testhstore where h ?& ARRAY['public','disabled']; 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; + +-- sort/hash +select count(distinct h) from testhstore; +set enable_hashagg = false; +select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2; +set enable_hashagg = true; +set enable_sort = false; +select count(*) from (select h from (select * from testhstore union all select * from testhstore) hs group by h) hs2; +select distinct * from (values (hstore '' || ''),('')) v(h); +set enable_sort = true; + +-- btree +drop index hidx; +create index hidx on testhstore using btree (h); +set enable_seqscan=off; + +select count(*) from testhstore where h #># 'p=>1'; +select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexed=>t'; diff --git a/contrib/hstore/uninstall_hstore.sql b/contrib/hstore/uninstall_hstore.sql index 17782d5c05..9162475ad1 100644 --- a/contrib/hstore/uninstall_hstore.sql +++ b/contrib/hstore/uninstall_hstore.sql @@ -1,36 +1,77 @@ -/* $PostgreSQL: pgsql/contrib/hstore/uninstall_hstore.sql,v 1.8 2009/03/25 22:19:01 tgl Exp $ */ +/* $PostgreSQL: pgsql/contrib/hstore/uninstall_hstore.sql,v 1.9 2009/09/30 19:50:22 tgl Exp $ */ -- Adjust this setting to control where the objects get dropped. SET search_path = public; DROP OPERATOR CLASS gist_hstore_ops USING gist CASCADE; DROP OPERATOR CLASS gin_hstore_ops USING gin CASCADE; +DROP OPERATOR CLASS hash_hstore_ops USING hash CASCADE; +DROP OPERATOR CLASS btree_hstore_ops USING btree CASCADE; -DROP OPERATOR ? ( hstore, text ); -DROP OPERATOR ->( hstore, text ); -DROP OPERATOR ||( hstore, hstore ); -DROP OPERATOR @>( hstore, hstore ); -DROP OPERATOR <@( hstore, hstore ); -DROP OPERATOR @( hstore, hstore ); -DROP OPERATOR ~( hstore, hstore ); -DROP OPERATOR =>( text, text ); +DROP OPERATOR - ( hstore, text ); +DROP OPERATOR - ( hstore, text[] ); +DROP OPERATOR - ( hstore, hstore ); +DROP OPERATOR ? ( hstore, text ); +DROP OPERATOR ?& ( hstore, text[] ); +DROP OPERATOR ?| ( hstore, text[] ); +DROP OPERATOR -> ( hstore, text ); +DROP OPERATOR -> ( hstore, text[] ); +DROP OPERATOR || ( hstore, hstore ); +DROP OPERATOR @> ( hstore, hstore ); +DROP OPERATOR <@ ( hstore, hstore ); +DROP OPERATOR @ ( hstore, hstore ); +DROP OPERATOR ~ ( hstore, hstore ); +DROP OPERATOR => ( text, text ); +DROP OPERATOR => ( text[], text[] ); +DROP OPERATOR => ( hstore, text[] ); +DROP OPERATOR #= ( anyelement, hstore ); +DROP OPERATOR %% ( NONE, hstore ); +DROP OPERATOR %# ( NONE, hstore ); +DROP OPERATOR = ( hstore, hstore ); +DROP OPERATOR <> ( hstore, hstore ); +DROP OPERATOR #<# ( hstore, hstore ); +DROP OPERATOR #<=# ( hstore, hstore ); +DROP OPERATOR #># ( hstore, hstore ); +DROP OPERATOR #>=# ( hstore, hstore ); +DROP CAST (text[] AS hstore); +DROP FUNCTION hstore_eq(hstore,hstore); +DROP FUNCTION hstore_ne(hstore,hstore); +DROP FUNCTION hstore_gt(hstore,hstore); +DROP FUNCTION hstore_ge(hstore,hstore); +DROP FUNCTION hstore_lt(hstore,hstore); +DROP FUNCTION hstore_le(hstore,hstore); +DROP FUNCTION hstore_cmp(hstore,hstore); +DROP FUNCTION hstore_hash(hstore); +DROP FUNCTION slice_array(hstore,text[]); +DROP FUNCTION slice_hstore(hstore,text[]); DROP FUNCTION fetchval(hstore,text); DROP FUNCTION isexists(hstore,text); DROP FUNCTION exist(hstore,text); +DROP FUNCTION exists_any(hstore,text[]); +DROP FUNCTION exists_all(hstore,text[]); DROP FUNCTION isdefined(hstore,text); DROP FUNCTION defined(hstore,text); DROP FUNCTION delete(hstore,text); +DROP FUNCTION delete(hstore,text[]); +DROP FUNCTION delete(hstore,hstore); DROP FUNCTION hs_concat(hstore,hstore); DROP FUNCTION hs_contains(hstore,hstore); DROP FUNCTION hs_contained(hstore,hstore); DROP FUNCTION tconvert(text,text); +DROP FUNCTION hstore(text,text); +DROP FUNCTION hstore(text[],text[]); +DROP FUNCTION hstore_to_array(hstore); +DROP FUNCTION hstore_to_matrix(hstore); +DROP FUNCTION hstore(record); +DROP FUNCTION hstore(text[]); DROP FUNCTION akeys(hstore); DROP FUNCTION avals(hstore); DROP FUNCTION skeys(hstore); DROP FUNCTION svals(hstore); DROP FUNCTION each(hstore); +DROP FUNCTION populate_record(anyelement,hstore); DROP FUNCTION ghstore_compress(internal); DROP FUNCTION ghstore_decompress(internal); DROP FUNCTION ghstore_penalty(internal,internal,internal); @@ -41,6 +82,7 @@ DROP FUNCTION ghstore_consistent(internal,internal,int,oid,internal); DROP FUNCTION gin_consistent_hstore(internal, int2, internal, int4, internal, internal); DROP FUNCTION gin_extract_hstore(internal, internal); DROP FUNCTION gin_extract_hstore_query(internal, internal, smallint, internal, internal); +DROP FUNCTION hstore_version_diag(hstore); DROP TYPE hstore CASCADE; DROP TYPE ghstore CASCADE; diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml index 48664b2b25..78a2eb57ca 100644 --- a/doc/src/sgml/hstore.sgml +++ b/doc/src/sgml/hstore.sgml @@ -1,4 +1,4 @@ - + hstore @@ -11,13 +11,8 @@ This module implements a data type hstore for storing sets of (key,value) pairs within a single PostgreSQL data field. This can be useful in various scenarios, such as rows with many attributes - that are rarely examined, or semi-structured data. - - - - In the current implementation, neither the key nor the value - string can exceed 65535 bytes in length; an error will be thrown if this - limit is exceeded. These maximum lengths may change in future releases. + that are rarely examined, or semi-structured data. Keys and values are + arbitrary text strings. @@ -39,9 +34,7 @@ => sign is ignored. Use double quotes if a key or value includes whitespace, comma, = or >. To include a double quote or a backslash in a key or value, precede - it with another backslash. (Keep in mind that depending on the - setting of standard_conforming_strings, you may need to - double backslashes in SQL literal strings.) + it with another backslash. @@ -56,8 +49,20 @@ as an ordinary data value. + + + Keep in mind that the above format, when used to input hstore values, + applies before any required quoting or escaping. If you + are passing an hstore literal via a parameter, then no additional + processing is needed. If you are passing it as a quoted literal + constant, then any single-quote characters and (depending on the + setting of standard_conforming_strings) backslash characters + need to be escaped correctly. See . + + + - Currently, double quotes are always used to surround key and value + Double quotes are always used to surround key and value strings on output, even when this is not strictly necessary. @@ -87,6 +92,13 @@ x + + hstore -> text[] + get values for keys (null if not present) + 'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a'] + {"z","x"} + + text => text make single-item hstore @@ -94,6 +106,20 @@ "a"=>"b" + + text[] => text[] + construct an hstore value from separate key and value arrays + ARRAY['a','b'] => ARRAY['1','2'] + "a"=>"1","b"=>"2" + + + + hstore => text[] + extract a subset of an hstore value + 'a=>1,b=>2,c=>3'::hstore => ARRAY['b','c','x'] + "b"=>"2", "c"=>"3" + + hstore || hstore concatenation @@ -108,6 +134,20 @@ t + + hstore ?& text[] + does hstore contain all specified keys? + 'a=>1,b=>2'::hstore ?& ARRAY['a','b'] + t + + + + hstore ?| text[] + does hstore contain any of the specified keys? + 'a=>1,b=>2'::hstore ?| ARRAY['b','c'] + t + + hstore @> hstore does left operand contain right? @@ -122,6 +162,48 @@ f + + hstore - text + delete key from left operand + 'a=>1, b=>2, c=>3'::hstore - 'b'::text + "a"=>"1", "c"=>"3" + + + + hstore - text[] + delete keys from left operand + 'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b'] + "c"=>"3" + + + + hstore - hstore + delete matching key/value pairs from left operand + 'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore + "a"=>"1", "c"=>"3" + + + + record #= hstore + replace fields in record with matching values from hstore + see Examples section + + + + + %% hstore + convert hstore to array of alternating keys and values + %% 'a=>foo, b=>bar'::hstore + {a,foo,b,bar} + + + + %# hstore + convert hstore to two-dimensional key/value array + %# 'a=>foo, b=>bar'::hstore + {{a,foo},{b,bar}} + + @@ -149,6 +231,23 @@ + + hstore(record) + hstore + construct an hstore from a record or row + hstore(ROW(1,2)) + f1=>1,f2=>2 + + + + hstore(text[]) + hstore + construct an hstore from an array, which may be either + a key/value array, or a two-dimensional array + hstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d','4']]) + a=>1, b=>2, c=>3, d=>4 + + akeys(hstore) text[] @@ -189,6 +288,23 @@ b + + hstore_to_array(hstore) + text[] + get hstore's keys and values as an array of alternating + keys and values + hstore_to_array('a=>1,b=>2') + {a,1,b,2} + + + + hstore_to_matrix(hstore) + text[] + get hstore's keys and values as a two-dimensional array + hstore_to_matrix('a=>1,b=>2') + {{a,1},{b,2}} + + each(hstore) setof (key text, value text) @@ -227,22 +343,71 @@ b "a"=>"1" + + delete(hstore,text[]) + hstore + delete any item matching any of the keys + delete('a=>1,b=>2,c=>3',ARRAY['a','b']) + "c"=>"3" + + + + delete(hstore,hstore) + hstore + delete any key/value pair with an exact match in the second argument + delete('a=>1,b=>2','a=>4,b=>2'::hstore) + "a"=>"1" + + + + populate_record(record,hstore) + record + replace fields in record with matching values from hstore + see Examples section + + + + + + + The function populate_record is actually declared + with anyelement, not record, as its first argument; + but it will reject non-record types with a runtime error. + + Indexes - hstore has index support for @> and ? - operators. You can use either GiST or GIN index types. For example: + hstore has index support for @>, ?, + ?& and ?| operators. You can use either + GiST or GIN index types. For example: -CREATE INDEX hidx ON testhstore USING GIST(h); +CREATE INDEX hidx ON testhstore USING GIST (h); -CREATE INDEX hidx ON testhstore USING GIN(h); +CREATE INDEX hidx ON testhstore USING GIN (h); + + + + Additionally, hstore has index support for the = + operator using the btree or hash index types. This + allows hstore columns to be declared UNIQUE, or used with + GROUP BY, ORDER BY or DISTINCT. The sort ordering for hstore + values is not intended to be particularly useful; it merely brings + exactly equal values together. + If an index is needed to support = comparisons it can be + created as follows: + + +CREATE INDEX hidx ON testhstore USING BTREE (h); + +CREATE INDEX hidx ON testhstore USING HASH (h); @@ -262,6 +427,48 @@ UPDATE tab SET h = h || ('c' => '3'); UPDATE tab SET h = delete(h, 'k1'); + + + Convert a record to an hstore: + + +CREATE TABLE test (col1 integer, col2 text, col3 text); +INSERT INTO test VALUES (123, 'foo', 'bar'); + +SELECT hstore(t) FROM test AS t; + hstore +--------------------------------------------- + "col1"=>"123", "col2"=>"foo", "col3"=>"bar" +(1 row) + + + + Convert an hstore to a predefined record type: + + +CREATE TABLE test (col1 integer, col2 text, col3 text); + +SELECT * FROM populate_record(null::test, + '"col1"=>"456", "col2"=>"zzz"'); + col1 | col2 | col3 +------+------+------ + 456 | zzz | +(1 row) + + + + Modify an existing record using the values from an hstore: + + +CREATE TABLE test (col1 integer, col2 text, col3 text); +INSERT INTO test VALUES (123, 'foo', 'bar'); + +SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s; + col1 | col2 | col3 +------+------+------ + 123 | foo | baz +(1 row) + @@ -311,6 +518,45 @@ SELECT key, count(*) FROM + + Compatibility + + + When upgrading from older versions, always load the new + version of this module into the database before restoring an old + dump. Otherwise, many new features will be unavailable. + + + + As of PostgreSQL 8.5, hstore uses a different internal + representation than previous versions. This presents no obstacle for + dump/restore upgrades since the text representation (used in the dump) is + unchanged. + + + + In the event of doing a binary upgrade, upward + compatibility is maintained by having the new code recognize + old-format data. This will entail a slight performance penalty when + processing data that has not yet been modified by the new code. It is + possible to force an upgrade of all values in a table column + by doing an UPDATE statement as follows: + + +UPDATE tablename SET hstorecol = hstorecol || ''; + + + + Another way to do it is: + +ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || ''; + + The ALTER TABLE method requires an exclusive lock on the table, + but does not result in bloating the table with old row versions. + + + + Authors @@ -321,6 +567,10 @@ SELECT key, count(*) FROM Teodor Sigaev teodor@sigaev.ru, Moscow, Delta-Soft Ltd., Russia + + + Additional enhancements by Andrew Gierth andrew@tao11.riddles.org.uk, United Kingdom + -- 2.40.0