commit 51217bac66009f876d44ee547c25523a1b0eaeb3 Author: Heikki Linnakangas Date: Mon Dec 22 17:38:40 2014 +0200 Rebase Teodor's btree_gin_range-1.patch over my refactorings. diff --git a/contrib/btree_gin/Makefile b/contrib/btree_gin/Makefile index 0492091..b85c390 100644 --- a/contrib/btree_gin/Makefile +++ b/contrib/btree_gin/Makefile @@ -4,7 +4,7 @@ MODULE_big = btree_gin OBJS = btree_gin.o $(WIN32RES) EXTENSION = btree_gin -DATA = btree_gin--1.0.sql btree_gin--unpackaged--1.0.sql +DATA = btree_gin--1.1.sql btree_gin--1.0--1.1.sql btree_gin--unpackaged--1.0.sql PGFILEDESC = "btree_gin - B-tree equivalent GIN operator classes" REGRESS = install_btree_gin int2 int4 int8 float4 float8 money oid \ diff --git a/contrib/btree_gin/btree_gin--1.0--1.1.sql b/contrib/btree_gin/btree_gin--1.0--1.1.sql new file mode 100644 index 0000000..a7f0e54 --- /dev/null +++ b/contrib/btree_gin/btree_gin--1.0--1.1.sql @@ -0,0 +1,7 @@ +ALTER OPERATOR FAMILY int4_ops USING gin ADD OPERATOR 8 <@(anyelement, anyrange); +ALTER OPERATOR FAMILY int8_ops USING gin ADD OPERATOR 8 <@(anyelement, anyrange); +ALTER OPERATOR FAMILY timestamp_ops USING gin ADD OPERATOR 8 <@(anyelement, anyrange); +ALTER OPERATOR FAMILY timestamptz_ops USING gin ADD OPERATOR 8 <@(anyelement, anyrange); +ALTER OPERATOR FAMILY date_ops USING gin ADD OPERATOR 8 <@(anyelement, anyrange); +ALTER OPERATOR FAMILY numeric_ops USING gin ADD OPERATOR 8 <@(anyelement, anyrange); + diff --git a/contrib/btree_gin/btree_gin--1.0.sql b/contrib/btree_gin/btree_gin--1.0.sql deleted file mode 100644 index cf867ef..0000000 --- a/contrib/btree_gin/btree_gin--1.0.sql +++ /dev/null @@ -1,689 +0,0 @@ -/* contrib/btree_gin/btree_gin--1.0.sql */ - --- complain if script is sourced in psql, rather than via CREATE EXTENSION -\echo Use "CREATE EXTENSION btree_gin" to load this file. \quit - -CREATE FUNCTION gin_btree_consistent(internal, int2, anyelement, int4, internal, internal) -RETURNS bool -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_value_int2(int2, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_int2(int2, int2, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_int2(int2, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS int2_ops -DEFAULT FOR TYPE int2 USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 btint2cmp(int2,int2), - FUNCTION 2 gin_extract_value_int2(int2, internal), - FUNCTION 3 gin_extract_query_int2(int2, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_int2(int2,int2,int2, internal), -STORAGE int2; - -CREATE FUNCTION gin_extract_value_int4(int4, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_int4(int4, int4, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_int4(int4, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS int4_ops -DEFAULT FOR TYPE int4 USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 btint4cmp(int4,int4), - FUNCTION 2 gin_extract_value_int4(int4, internal), - FUNCTION 3 gin_extract_query_int4(int4, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_int4(int4,int4,int2, internal), -STORAGE int4; - -CREATE FUNCTION gin_extract_value_int8(int8, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_int8(int8, int8, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_int8(int8, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS int8_ops -DEFAULT FOR TYPE int8 USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 btint8cmp(int8,int8), - FUNCTION 2 gin_extract_value_int8(int8, internal), - FUNCTION 3 gin_extract_query_int8(int8, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_int8(int8,int8,int2, internal), -STORAGE int8; - -CREATE FUNCTION gin_extract_value_float4(float4, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_float4(float4, float4, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_float4(float4, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS float4_ops -DEFAULT FOR TYPE float4 USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 btfloat4cmp(float4,float4), - FUNCTION 2 gin_extract_value_float4(float4, internal), - FUNCTION 3 gin_extract_query_float4(float4, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_float4(float4,float4,int2, internal), -STORAGE float4; - -CREATE FUNCTION gin_extract_value_float8(float8, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_float8(float8, float8, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_float8(float8, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS float8_ops -DEFAULT FOR TYPE float8 USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 btfloat8cmp(float8,float8), - FUNCTION 2 gin_extract_value_float8(float8, internal), - FUNCTION 3 gin_extract_query_float8(float8, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_float8(float8,float8,int2, internal), -STORAGE float8; - -CREATE FUNCTION gin_extract_value_money(money, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_money(money, money, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_money(money, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS money_ops -DEFAULT FOR TYPE money USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 cash_cmp(money,money), - FUNCTION 2 gin_extract_value_money(money, internal), - FUNCTION 3 gin_extract_query_money(money, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_money(money,money,int2, internal), -STORAGE money; - -CREATE FUNCTION gin_extract_value_oid(oid, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_oid(oid, oid, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_oid(oid, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS oid_ops -DEFAULT FOR TYPE oid USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 btoidcmp(oid,oid), - FUNCTION 2 gin_extract_value_oid(oid, internal), - FUNCTION 3 gin_extract_query_oid(oid, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_oid(oid,oid,int2, internal), -STORAGE oid; - -CREATE FUNCTION gin_extract_value_timestamp(timestamp, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_timestamp(timestamp, timestamp, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_timestamp(timestamp, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS timestamp_ops -DEFAULT FOR TYPE timestamp USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 timestamp_cmp(timestamp,timestamp), - FUNCTION 2 gin_extract_value_timestamp(timestamp, internal), - FUNCTION 3 gin_extract_query_timestamp(timestamp, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_timestamp(timestamp,timestamp,int2, internal), -STORAGE timestamp; - -CREATE FUNCTION gin_extract_value_timestamptz(timestamptz, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_timestamptz(timestamptz, timestamptz, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_timestamptz(timestamptz, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS timestamptz_ops -DEFAULT FOR TYPE timestamptz USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 timestamptz_cmp(timestamptz,timestamptz), - FUNCTION 2 gin_extract_value_timestamptz(timestamptz, internal), - FUNCTION 3 gin_extract_query_timestamptz(timestamptz, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_timestamptz(timestamptz,timestamptz,int2, internal), -STORAGE timestamptz; - -CREATE FUNCTION gin_extract_value_time(time, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_time(time, time, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_time(time, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS time_ops -DEFAULT FOR TYPE time USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 time_cmp(time,time), - FUNCTION 2 gin_extract_value_time(time, internal), - FUNCTION 3 gin_extract_query_time(time, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_time(time,time,int2, internal), -STORAGE time; - -CREATE FUNCTION gin_extract_value_timetz(timetz, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_timetz(timetz, timetz, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_timetz(timetz, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS timetz_ops -DEFAULT FOR TYPE timetz USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 timetz_cmp(timetz,timetz), - FUNCTION 2 gin_extract_value_timetz(timetz, internal), - FUNCTION 3 gin_extract_query_timetz(timetz, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_timetz(timetz,timetz,int2, internal), -STORAGE timetz; - -CREATE FUNCTION gin_extract_value_date(date, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_date(date, date, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_date(date, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS date_ops -DEFAULT FOR TYPE date USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 date_cmp(date,date), - FUNCTION 2 gin_extract_value_date(date, internal), - FUNCTION 3 gin_extract_query_date(date, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_date(date,date,int2, internal), -STORAGE date; - -CREATE FUNCTION gin_extract_value_interval(interval, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_interval(interval, interval, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_interval(interval, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS interval_ops -DEFAULT FOR TYPE interval USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 interval_cmp(interval,interval), - FUNCTION 2 gin_extract_value_interval(interval, internal), - FUNCTION 3 gin_extract_query_interval(interval, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_interval(interval,interval,int2, internal), -STORAGE interval; - -CREATE FUNCTION gin_extract_value_macaddr(macaddr, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_macaddr(macaddr, macaddr, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_macaddr(macaddr, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS macaddr_ops -DEFAULT FOR TYPE macaddr USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 macaddr_cmp(macaddr,macaddr), - FUNCTION 2 gin_extract_value_macaddr(macaddr, internal), - FUNCTION 3 gin_extract_query_macaddr(macaddr, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_macaddr(macaddr,macaddr,int2, internal), -STORAGE macaddr; - -CREATE FUNCTION gin_extract_value_inet(inet, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_inet(inet, inet, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_inet(inet, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS inet_ops -DEFAULT FOR TYPE inet USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 network_cmp(inet,inet), - FUNCTION 2 gin_extract_value_inet(inet, internal), - FUNCTION 3 gin_extract_query_inet(inet, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_inet(inet,inet,int2, internal), -STORAGE inet; - -CREATE FUNCTION gin_extract_value_cidr(cidr, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_cidr(cidr, cidr, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_cidr(cidr, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS cidr_ops -DEFAULT FOR TYPE cidr USING gin -AS - OPERATOR 1 <(inet,inet), - OPERATOR 2 <=(inet,inet), - OPERATOR 3 =(inet,inet), - OPERATOR 4 >=(inet,inet), - OPERATOR 5 >(inet,inet), - FUNCTION 1 network_cmp(inet,inet), - FUNCTION 2 gin_extract_value_cidr(cidr, internal), - FUNCTION 3 gin_extract_query_cidr(cidr, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_cidr(cidr,cidr,int2, internal), -STORAGE cidr; - -CREATE FUNCTION gin_extract_value_text(text, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_text(text, text, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_text(text, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS text_ops -DEFAULT FOR TYPE text USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 bttextcmp(text,text), - FUNCTION 2 gin_extract_value_text(text, internal), - FUNCTION 3 gin_extract_query_text(text, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_text(text,text,int2, internal), -STORAGE text; - -CREATE OPERATOR CLASS varchar_ops -DEFAULT FOR TYPE varchar USING gin -AS - OPERATOR 1 <(text,text), - OPERATOR 2 <=(text,text), - OPERATOR 3 =(text,text), - OPERATOR 4 >=(text,text), - OPERATOR 5 >(text,text), - FUNCTION 1 bttextcmp(text,text), - FUNCTION 2 gin_extract_value_text(text, internal), - FUNCTION 3 gin_extract_query_text(text, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_text(text,text,int2, internal), -STORAGE varchar; - -CREATE FUNCTION gin_extract_value_char("char", internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_char("char", "char", int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_char("char", internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS char_ops -DEFAULT FOR TYPE "char" USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 btcharcmp("char","char"), - FUNCTION 2 gin_extract_value_char("char", internal), - FUNCTION 3 gin_extract_query_char("char", internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_char("char","char",int2, internal), -STORAGE "char"; - -CREATE FUNCTION gin_extract_value_bytea(bytea, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_bytea(bytea, bytea, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_bytea(bytea, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS bytea_ops -DEFAULT FOR TYPE bytea USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 byteacmp(bytea,bytea), - FUNCTION 2 gin_extract_value_bytea(bytea, internal), - FUNCTION 3 gin_extract_query_bytea(bytea, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_bytea(bytea,bytea,int2, internal), -STORAGE bytea; - -CREATE FUNCTION gin_extract_value_bit(bit, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_bit(bit, bit, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_bit(bit, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS bit_ops -DEFAULT FOR TYPE bit USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 bitcmp(bit,bit), - FUNCTION 2 gin_extract_value_bit(bit, internal), - FUNCTION 3 gin_extract_query_bit(bit, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_bit(bit,bit,int2, internal), -STORAGE bit; - -CREATE FUNCTION gin_extract_value_varbit(varbit, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_varbit(varbit, varbit, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_varbit(varbit, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS varbit_ops -DEFAULT FOR TYPE varbit USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 varbitcmp(varbit,varbit), - FUNCTION 2 gin_extract_value_varbit(varbit, internal), - FUNCTION 3 gin_extract_query_varbit(varbit, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_varbit(varbit,varbit,int2, internal), -STORAGE varbit; - -CREATE FUNCTION gin_extract_value_numeric(numeric, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_compare_prefix_numeric(numeric, numeric, int2, internal) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_extract_query_numeric(numeric, internal, int2, internal, internal) -RETURNS internal -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE FUNCTION gin_numeric_cmp(numeric, numeric) -RETURNS int4 -AS 'MODULE_PATHNAME' -LANGUAGE C STRICT IMMUTABLE; - -CREATE OPERATOR CLASS numeric_ops -DEFAULT FOR TYPE numeric USING gin -AS - OPERATOR 1 <, - OPERATOR 2 <=, - OPERATOR 3 =, - OPERATOR 4 >=, - OPERATOR 5 >, - FUNCTION 1 gin_numeric_cmp(numeric,numeric), - FUNCTION 2 gin_extract_value_numeric(numeric, internal), - FUNCTION 3 gin_extract_query_numeric(numeric, internal, int2, internal, internal), - FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), - FUNCTION 5 gin_compare_prefix_numeric(numeric,numeric,int2, internal), -STORAGE numeric; diff --git a/contrib/btree_gin/btree_gin--1.1.sql b/contrib/btree_gin/btree_gin--1.1.sql new file mode 100644 index 0000000..c5980a1 --- /dev/null +++ b/contrib/btree_gin/btree_gin--1.1.sql @@ -0,0 +1,695 @@ +/* contrib/btree_gin/btree_gin--1.1.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION btree_gin" to load this file. \quit + +CREATE FUNCTION gin_btree_consistent(internal, int2, anyelement, int4, internal, internal) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_value_int2(int2, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_int2(int2, int2, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_int2(int2, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS int2_ops +DEFAULT FOR TYPE int2 USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btint2cmp(int2,int2), + FUNCTION 2 gin_extract_value_int2(int2, internal), + FUNCTION 3 gin_extract_query_int2(int2, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_int2(int2,int2,int2, internal), +STORAGE int2; + +CREATE FUNCTION gin_extract_value_int4(int4, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_int4(int4, int4, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_int4(int4, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS int4_ops +DEFAULT FOR TYPE int4 USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + OPERATOR 8 <@(anyelement, anyrange), + FUNCTION 1 btint4cmp(int4,int4), + FUNCTION 2 gin_extract_value_int4(int4, internal), + FUNCTION 3 gin_extract_query_int4(int4, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_int4(int4,int4,int2, internal), +STORAGE int4; + +CREATE FUNCTION gin_extract_value_int8(int8, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_int8(int8, int8, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_int8(int8, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS int8_ops +DEFAULT FOR TYPE int8 USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + OPERATOR 8 <@(anyelement, anyrange), + FUNCTION 1 btint8cmp(int8,int8), + FUNCTION 2 gin_extract_value_int8(int8, internal), + FUNCTION 3 gin_extract_query_int8(int8, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_int8(int8,int8,int2, internal), +STORAGE int8; + +CREATE FUNCTION gin_extract_value_float4(float4, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_float4(float4, float4, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_float4(float4, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS float4_ops +DEFAULT FOR TYPE float4 USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btfloat4cmp(float4,float4), + FUNCTION 2 gin_extract_value_float4(float4, internal), + FUNCTION 3 gin_extract_query_float4(float4, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_float4(float4,float4,int2, internal), +STORAGE float4; + +CREATE FUNCTION gin_extract_value_float8(float8, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_float8(float8, float8, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_float8(float8, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS float8_ops +DEFAULT FOR TYPE float8 USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btfloat8cmp(float8,float8), + FUNCTION 2 gin_extract_value_float8(float8, internal), + FUNCTION 3 gin_extract_query_float8(float8, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_float8(float8,float8,int2, internal), +STORAGE float8; + +CREATE FUNCTION gin_extract_value_money(money, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_money(money, money, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_money(money, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS money_ops +DEFAULT FOR TYPE money USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 cash_cmp(money,money), + FUNCTION 2 gin_extract_value_money(money, internal), + FUNCTION 3 gin_extract_query_money(money, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_money(money,money,int2, internal), +STORAGE money; + +CREATE FUNCTION gin_extract_value_oid(oid, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_oid(oid, oid, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_oid(oid, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS oid_ops +DEFAULT FOR TYPE oid USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btoidcmp(oid,oid), + FUNCTION 2 gin_extract_value_oid(oid, internal), + FUNCTION 3 gin_extract_query_oid(oid, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_oid(oid,oid,int2, internal), +STORAGE oid; + +CREATE FUNCTION gin_extract_value_timestamp(timestamp, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_timestamp(timestamp, timestamp, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_timestamp(timestamp, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS timestamp_ops +DEFAULT FOR TYPE timestamp USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + OPERATOR 8 <@(anyelement, anyrange), + FUNCTION 1 timestamp_cmp(timestamp,timestamp), + FUNCTION 2 gin_extract_value_timestamp(timestamp, internal), + FUNCTION 3 gin_extract_query_timestamp(timestamp, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_timestamp(timestamp,timestamp,int2, internal), +STORAGE timestamp; + +CREATE FUNCTION gin_extract_value_timestamptz(timestamptz, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_timestamptz(timestamptz, timestamptz, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_timestamptz(timestamptz, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS timestamptz_ops +DEFAULT FOR TYPE timestamptz USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + OPERATOR 8 <@(anyelement, anyrange), + FUNCTION 1 timestamptz_cmp(timestamptz,timestamptz), + FUNCTION 2 gin_extract_value_timestamptz(timestamptz, internal), + FUNCTION 3 gin_extract_query_timestamptz(timestamptz, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_timestamptz(timestamptz,timestamptz,int2, internal), +STORAGE timestamptz; + +CREATE FUNCTION gin_extract_value_time(time, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_time(time, time, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_time(time, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS time_ops +DEFAULT FOR TYPE time USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 time_cmp(time,time), + FUNCTION 2 gin_extract_value_time(time, internal), + FUNCTION 3 gin_extract_query_time(time, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_time(time,time,int2, internal), +STORAGE time; + +CREATE FUNCTION gin_extract_value_timetz(timetz, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_timetz(timetz, timetz, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_timetz(timetz, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS timetz_ops +DEFAULT FOR TYPE timetz USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 timetz_cmp(timetz,timetz), + FUNCTION 2 gin_extract_value_timetz(timetz, internal), + FUNCTION 3 gin_extract_query_timetz(timetz, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_timetz(timetz,timetz,int2, internal), +STORAGE timetz; + +CREATE FUNCTION gin_extract_value_date(date, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_date(date, date, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_date(date, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS date_ops +DEFAULT FOR TYPE date USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + OPERATOR 8 <@(anyelement, anyrange), + FUNCTION 1 date_cmp(date,date), + FUNCTION 2 gin_extract_value_date(date, internal), + FUNCTION 3 gin_extract_query_date(date, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_date(date,date,int2, internal), +STORAGE date; + +CREATE FUNCTION gin_extract_value_interval(interval, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_interval(interval, interval, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_interval(interval, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS interval_ops +DEFAULT FOR TYPE interval USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 interval_cmp(interval,interval), + FUNCTION 2 gin_extract_value_interval(interval, internal), + FUNCTION 3 gin_extract_query_interval(interval, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_interval(interval,interval,int2, internal), +STORAGE interval; + +CREATE FUNCTION gin_extract_value_macaddr(macaddr, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_macaddr(macaddr, macaddr, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_macaddr(macaddr, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS macaddr_ops +DEFAULT FOR TYPE macaddr USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 macaddr_cmp(macaddr,macaddr), + FUNCTION 2 gin_extract_value_macaddr(macaddr, internal), + FUNCTION 3 gin_extract_query_macaddr(macaddr, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_macaddr(macaddr,macaddr,int2, internal), +STORAGE macaddr; + +CREATE FUNCTION gin_extract_value_inet(inet, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_inet(inet, inet, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_inet(inet, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS inet_ops +DEFAULT FOR TYPE inet USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 network_cmp(inet,inet), + FUNCTION 2 gin_extract_value_inet(inet, internal), + FUNCTION 3 gin_extract_query_inet(inet, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_inet(inet,inet,int2, internal), +STORAGE inet; + +CREATE FUNCTION gin_extract_value_cidr(cidr, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_cidr(cidr, cidr, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_cidr(cidr, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS cidr_ops +DEFAULT FOR TYPE cidr USING gin +AS + OPERATOR 1 <(inet,inet), + OPERATOR 2 <=(inet,inet), + OPERATOR 3 =(inet,inet), + OPERATOR 4 >=(inet,inet), + OPERATOR 5 >(inet,inet), + FUNCTION 1 network_cmp(inet,inet), + FUNCTION 2 gin_extract_value_cidr(cidr, internal), + FUNCTION 3 gin_extract_query_cidr(cidr, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_cidr(cidr,cidr,int2, internal), +STORAGE cidr; + +CREATE FUNCTION gin_extract_value_text(text, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_text(text, text, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_text(text, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS text_ops +DEFAULT FOR TYPE text USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 bttextcmp(text,text), + FUNCTION 2 gin_extract_value_text(text, internal), + FUNCTION 3 gin_extract_query_text(text, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_text(text,text,int2, internal), +STORAGE text; + +CREATE OPERATOR CLASS varchar_ops +DEFAULT FOR TYPE varchar USING gin +AS + OPERATOR 1 <(text,text), + OPERATOR 2 <=(text,text), + OPERATOR 3 =(text,text), + OPERATOR 4 >=(text,text), + OPERATOR 5 >(text,text), + FUNCTION 1 bttextcmp(text,text), + FUNCTION 2 gin_extract_value_text(text, internal), + FUNCTION 3 gin_extract_query_text(text, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_text(text,text,int2, internal), +STORAGE varchar; + +CREATE FUNCTION gin_extract_value_char("char", internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_char("char", "char", int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_char("char", internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS char_ops +DEFAULT FOR TYPE "char" USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btcharcmp("char","char"), + FUNCTION 2 gin_extract_value_char("char", internal), + FUNCTION 3 gin_extract_query_char("char", internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_char("char","char",int2, internal), +STORAGE "char"; + +CREATE FUNCTION gin_extract_value_bytea(bytea, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_bytea(bytea, bytea, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_bytea(bytea, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS bytea_ops +DEFAULT FOR TYPE bytea USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 byteacmp(bytea,bytea), + FUNCTION 2 gin_extract_value_bytea(bytea, internal), + FUNCTION 3 gin_extract_query_bytea(bytea, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_bytea(bytea,bytea,int2, internal), +STORAGE bytea; + +CREATE FUNCTION gin_extract_value_bit(bit, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_bit(bit, bit, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_bit(bit, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS bit_ops +DEFAULT FOR TYPE bit USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 bitcmp(bit,bit), + FUNCTION 2 gin_extract_value_bit(bit, internal), + FUNCTION 3 gin_extract_query_bit(bit, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_bit(bit,bit,int2, internal), +STORAGE bit; + +CREATE FUNCTION gin_extract_value_varbit(varbit, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_varbit(varbit, varbit, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_varbit(varbit, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS varbit_ops +DEFAULT FOR TYPE varbit USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 varbitcmp(varbit,varbit), + FUNCTION 2 gin_extract_value_varbit(varbit, internal), + FUNCTION 3 gin_extract_query_varbit(varbit, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_varbit(varbit,varbit,int2, internal), +STORAGE varbit; + +CREATE FUNCTION gin_extract_value_numeric(numeric, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_numeric(numeric, numeric, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_numeric(numeric, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_numeric_cmp(numeric, numeric) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS numeric_ops +DEFAULT FOR TYPE numeric USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + OPERATOR 8 <@(anyelement, anyrange), + FUNCTION 1 gin_numeric_cmp(numeric,numeric), + FUNCTION 2 gin_extract_value_numeric(numeric, internal), + FUNCTION 3 gin_extract_query_numeric(numeric, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_numeric(numeric,numeric,int2, internal), +STORAGE numeric; diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c index 80521fb..95ce9a6 100644 --- a/contrib/btree_gin/btree_gin.c +++ b/contrib/btree_gin/btree_gin.c @@ -5,6 +5,8 @@ #include +#include "access/gin.h" +#include "access/gist.h" #include "access/skey.h" #include "utils/builtins.h" #include "utils/bytea.h" @@ -12,6 +14,7 @@ #include "utils/date.h" #include "utils/inet.h" #include "utils/numeric.h" +#include "utils/rangetypes.h" #include "utils/timestamp.h" #include "utils/varbit.h" @@ -21,6 +24,8 @@ typedef struct QueryInfo { StrategyNumber strategy; Datum datum; + RangeBound lower; + RangeBound upper; bool is_varlena; Datum (*typecmp) (FunctionCallInfo); } QueryInfo; @@ -28,6 +33,23 @@ typedef struct QueryInfo /*** GIN support functions shared by all datatypes ***/ +static void +get_bounds(Datum r, QueryInfo *data) +{ + RangeType *range = DatumGetRangeType(r); + TypeCacheEntry *typcache; + bool empty; + + typcache = lookup_type_cache(RangeTypeGetOid(range), TYPECACHE_RANGE_INFO); + if (typcache->rngelemtype == NULL) + elog(ERROR, "type %u is not a range type", RangeTypeGetOid(range)); + + range_deserialize(typcache, range, &data->lower, &data->upper, &empty); + + if (empty) + data->lower.infinite = data->upper.infinite = true; +} + static Datum gin_btree_extract_value(FunctionCallInfo fcinfo, bool is_varlena) { @@ -62,15 +84,19 @@ gin_btree_extract_query(FunctionCallInfo fcinfo, StrategyNumber strategy = PG_GETARG_UINT16(2); bool **partialmatch = (bool **) PG_GETARG_POINTER(3); Pointer **extra_data = (Pointer **) PG_GETARG_POINTER(4); + int32 *searchMode = (int32 *) PG_GETARG_POINTER(6); Datum *entries = (Datum *) palloc(sizeof(Datum)); QueryInfo *data = (QueryInfo *) palloc(sizeof(QueryInfo)); bool *ptr_partialmatch; + if (strategy == RTContainedByStrategyNumber /* range type */ || + is_varlena) + datum = PointerGetDatum(PG_DETOAST_DATUM(datum)); + *nentries = 1; ptr_partialmatch = *partialmatch = (bool *) palloc(sizeof(bool)); *ptr_partialmatch = false; - if (is_varlena) - datum = PointerGetDatum(PG_DETOAST_DATUM(datum)); + *searchMode = GIN_SEARCH_MODE_DEFAULT; data->strategy = strategy; data->datum = datum; data->is_varlena = is_varlena; @@ -78,7 +104,37 @@ gin_btree_extract_query(FunctionCallInfo fcinfo, *extra_data = (Pointer *) palloc(sizeof(Pointer)); **extra_data = (Pointer) data; - switch (strategy) + if (strategy == RTContainedByStrategyNumber) + { + get_bounds(datum, data); + + if (data->lower.infinite && data->upper.infinite) + { + *nentries = 0; + *searchMode = GIN_SEARCH_MODE_ALL; + PG_RETURN_POINTER(entries); + } + else if (data->lower.infinite) + { + data->strategy = data->upper.inclusive ? + BTLessEqualStrategyNumber : BTLessStrategyNumber; + data->datum = data->upper.val; + } + else if (data->upper.infinite) + { + data->strategy = data->lower.inclusive ? + BTGreaterEqualStrategyNumber : BTGreaterStrategyNumber; + data->datum = data->lower.val; + } + else + { + *ptr_partialmatch = true; + entries[0] = data->lower.val; + PG_RETURN_POINTER(entries); + } + } + + switch (data->strategy) { case BTLessStrategyNumber: case BTLessEqualStrategyNumber: @@ -89,7 +145,7 @@ gin_btree_extract_query(FunctionCallInfo fcinfo, case BTGreaterStrategyNumber: *ptr_partialmatch = true; case BTEqualStrategyNumber: - entries[0] = datum; + entries[0] = data->datum; break; default: elog(ERROR, "unrecognized strategy number: %d", strategy); @@ -98,6 +154,10 @@ gin_btree_extract_query(FunctionCallInfo fcinfo, PG_RETURN_POINTER(entries); } +#define STOP_SCAN (1) +#define MATCH_SCAN (0) +#define CONT_SCAN (-1) + /* * Datum a is a value from extract_query method and for BTLess* * strategy it is a left-most value. So, use original datum from QueryInfo @@ -112,6 +172,36 @@ gin_btree_compare_prefix(FunctionCallInfo fcinfo) int32 res, cmp; + if (data->strategy == RTContainedByStrategyNumber) + { + cmp = DatumGetInt32(DirectFunctionCall2Coll( + data->typecmp, + PG_GET_COLLATION(), a, b)); + + if (cmp < 0) + { + cmp = DatumGetInt32(DirectFunctionCall2Coll( + data->typecmp, + PG_GET_COLLATION(), data->upper.val, b)); + if (cmp < 0) + res = STOP_SCAN; + else if (cmp == 0) + res = (data->upper.inclusive) ? MATCH_SCAN : STOP_SCAN; + else + res = MATCH_SCAN; + } + else if (cmp == 0) + { + res = (data->lower.inclusive) ? MATCH_SCAN : CONT_SCAN; + } + else + { + res = STOP_SCAN; + } + + PG_RETURN_INT32(res); + } + cmp = DatumGetInt32(DirectFunctionCall2Coll( data->typecmp, PG_GET_COLLATION(), @@ -125,44 +215,44 @@ gin_btree_compare_prefix(FunctionCallInfo fcinfo) case BTLessStrategyNumber: /* If original datum > indexed one then return match */ if (cmp > 0) - res = 0; + res = MATCH_SCAN; else - res = 1; + res = STOP_SCAN; break; case BTLessEqualStrategyNumber: /* The same except equality */ if (cmp >= 0) - res = 0; + res = MATCH_SCAN; else - res = 1; + res = STOP_SCAN; break; case BTEqualStrategyNumber: if (cmp != 0) - res = 1; + res = STOP_SCAN; else - res = 0; + res = MATCH_SCAN; break; case BTGreaterEqualStrategyNumber: /* If original datum <= indexed one then return match */ if (cmp <= 0) - res = 0; + res = MATCH_SCAN; else - res = 1; + res = STOP_SCAN; break; case BTGreaterStrategyNumber: /* If original datum <= indexed one then return match */ /* If original datum == indexed one then continue scan */ if (cmp < 0) - res = 0; + res = MATCH_SCAN; else if (cmp == 0) - res = -1; + res = CONT_SCAN; else - res = 1; + res = STOP_SCAN; break; default: elog(ERROR, "unrecognized strategy number: %d", data->strategy); - res = 0; + res = MATCH_SCAN; } PG_RETURN_INT32(res); diff --git a/contrib/btree_gin/btree_gin.control b/contrib/btree_gin/btree_gin.control index 3b2cb2d..d96436e 100644 --- a/contrib/btree_gin/btree_gin.control +++ b/contrib/btree_gin/btree_gin.control @@ -1,5 +1,5 @@ # btree_gin extension comment = 'support for indexing common datatypes in GIN' -default_version = '1.0' +default_version = '1.1' module_pathname = '$libdir/btree_gin' relocatable = true diff --git a/contrib/btree_gin/expected/date.out b/contrib/btree_gin/expected/date.out index 40dfa30..162759f 100644 --- a/contrib/btree_gin/expected/date.out +++ b/contrib/btree_gin/expected/date.out @@ -49,3 +49,73 @@ SELECT * FROM test_date WHERE i>'2004-10-26'::date ORDER BY i; 10-28-2004 (2 rows) +SELECT * FROM test_date WHERE i <@ '[2004-10-24, 2004-10-27]'::daterange ORDER BY i; + i +------------ + 10-24-2004 + 10-25-2004 + 10-26-2004 + 10-27-2004 +(4 rows) + +SELECT * FROM test_date WHERE i <@ '(2004-10-24, 2004-10-27]'::daterange ORDER BY i; + i +------------ + 10-25-2004 + 10-26-2004 + 10-27-2004 +(3 rows) + +SELECT * FROM test_date WHERE i <@ '[2004-10-24, 2004-10-27)'::daterange ORDER BY i; + i +------------ + 10-24-2004 + 10-25-2004 + 10-26-2004 +(3 rows) + +SELECT * FROM test_date WHERE i <@ '(2004-10-24, 2004-10-27)'::daterange ORDER BY i; + i +------------ + 10-25-2004 + 10-26-2004 +(2 rows) + +SELECT * FROM test_date WHERE i <@ daterange('2004-10-24', '2004-10-27') ORDER BY i; + i +------------ + 10-24-2004 + 10-25-2004 + 10-26-2004 +(3 rows) + +SELECT * FROM test_date WHERE i <@ daterange(NULL, '2004-10-27') ORDER BY i; + i +------------ + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 +(4 rows) + +SELECT * FROM test_date WHERE i <@ daterange('2004-10-24', NULL) ORDER BY i; + i +------------ + 10-24-2004 + 10-25-2004 + 10-26-2004 + 10-27-2004 + 10-28-2004 +(5 rows) + +SELECT * FROM test_date WHERE i <@ daterange(NULL, NULL) ORDER BY i; + i +------------ + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 + 10-27-2004 + 10-28-2004 +(6 rows) + diff --git a/contrib/btree_gin/expected/int4.out b/contrib/btree_gin/expected/int4.out index 0f0122c..923a56c 100644 --- a/contrib/btree_gin/expected/int4.out +++ b/contrib/btree_gin/expected/int4.out @@ -42,3 +42,67 @@ SELECT * FROM test_int4 WHERE i>1::int4 ORDER BY i; 3 (2 rows) +SELECT * FROM test_int4 WHERE i <@ '[-1, 1]'::int4range ORDER BY i; + i +---- + -1 + 0 + 1 +(3 rows) + +SELECT * FROM test_int4 WHERE i <@ '(-1, 1]'::int4range ORDER BY i; + i +--- + 0 + 1 +(2 rows) + +SELECT * FROM test_int4 WHERE i <@ '[-1, 1)'::int4range ORDER BY i; + i +---- + -1 + 0 +(2 rows) + +SELECT * FROM test_int4 WHERE i <@ '(-1, 1)'::int4range ORDER BY i; + i +--- + 0 +(1 row) + +SELECT * FROM test_int4 WHERE i <@ int4range(-1, 1) ORDER BY i; + i +---- + -1 + 0 +(2 rows) + +SELECT * FROM test_int4 WHERE i <@ int4range(NULL, 1) ORDER BY i; + i +---- + -2 + -1 + 0 +(3 rows) + +SELECT * FROM test_int4 WHERE i <@ int4range(-1, NULL) ORDER BY i; + i +---- + -1 + 0 + 1 + 2 + 3 +(5 rows) + +SELECT * FROM test_int4 WHERE i <@ int4range(NULL, NULL) ORDER BY i; + i +---- + -2 + -1 + 0 + 1 + 2 + 3 +(6 rows) + diff --git a/contrib/btree_gin/expected/int8.out b/contrib/btree_gin/expected/int8.out index 307e19e..c7067eb 100644 --- a/contrib/btree_gin/expected/int8.out +++ b/contrib/btree_gin/expected/int8.out @@ -42,3 +42,67 @@ SELECT * FROM test_int8 WHERE i>1::int8 ORDER BY i; 3 (2 rows) +SELECT * FROM test_int8 WHERE i <@ '[-1, 1]'::int8range ORDER BY i; + i +---- + -1 + 0 + 1 +(3 rows) + +SELECT * FROM test_int8 WHERE i <@ '(-1, 1]'::int8range ORDER BY i; + i +--- + 0 + 1 +(2 rows) + +SELECT * FROM test_int8 WHERE i <@ '[-1, 1)'::int8range ORDER BY i; + i +---- + -1 + 0 +(2 rows) + +SELECT * FROM test_int8 WHERE i <@ '(-1, 1)'::int8range ORDER BY i; + i +--- + 0 +(1 row) + +SELECT * FROM test_int8 WHERE i <@ int8range(-1, 1) ORDER BY i; + i +---- + -1 + 0 +(2 rows) + +SELECT * FROM test_int8 WHERE i <@ int8range(NULL, 1) ORDER BY i; + i +---- + -2 + -1 + 0 +(3 rows) + +SELECT * FROM test_int8 WHERE i <@ int8range(-1, NULL) ORDER BY i; + i +---- + -1 + 0 + 1 + 2 + 3 +(5 rows) + +SELECT * FROM test_int8 WHERE i <@ int8range(NULL, NULL) ORDER BY i; + i +---- + -2 + -1 + 0 + 1 + 2 + 3 +(6 rows) + diff --git a/contrib/btree_gin/expected/numeric.out b/contrib/btree_gin/expected/numeric.out index f10a672..461f9d1 100644 --- a/contrib/btree_gin/expected/numeric.out +++ b/contrib/btree_gin/expected/numeric.out @@ -42,3 +42,67 @@ SELECT * FROM test_numeric WHERE i>'1'::numeric ORDER BY i; 3 (2 rows) +SELECT * FROM test_numeric WHERE i <@ '[-1, 1]'::numrange ORDER BY i; + i +---- + -1 + 0 + 1 +(3 rows) + +SELECT * FROM test_numeric WHERE i <@ '(-1, 1]'::numrange ORDER BY i; + i +--- + 0 + 1 +(2 rows) + +SELECT * FROM test_numeric WHERE i <@ '[-1, 1)'::numrange ORDER BY i; + i +---- + -1 + 0 +(2 rows) + +SELECT * FROM test_numeric WHERE i <@ '(-1, 1)'::numrange ORDER BY i; + i +--- + 0 +(1 row) + +SELECT * FROM test_numeric WHERE i <@ numrange(-1, 1) ORDER BY i; + i +---- + -1 + 0 +(2 rows) + +SELECT * FROM test_numeric WHERE i <@ numrange(NULL, 1) ORDER BY i; + i +---- + -2 + -1 + 0 +(3 rows) + +SELECT * FROM test_numeric WHERE i <@ numrange(-1, NULL) ORDER BY i; + i +---- + -1 + 0 + 1 + 2 + 3 +(5 rows) + +SELECT * FROM test_numeric WHERE i <@ numrange(NULL, NULL) ORDER BY i; + i +---- + -2 + -1 + 0 + 1 + 2 + 3 +(6 rows) + diff --git a/contrib/btree_gin/expected/timestamp.out b/contrib/btree_gin/expected/timestamp.out index a236cdc..1feb7e9 100644 --- a/contrib/btree_gin/expected/timestamp.out +++ b/contrib/btree_gin/expected/timestamp.out @@ -49,3 +49,73 @@ SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i Tue Oct 26 10:55:08 2004 (2 rows) +SELECT * FROM test_timestamp WHERE i <@ '[2004-10-26 04:55:08, 2004-10-26 09:55:08]'::tsrange ORDER BY i; + i +-------------------------- + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Tue Oct 26 09:55:08 2004 +(4 rows) + +SELECT * FROM test_timestamp WHERE i <@ '(2004-10-26 04:55:08, 2004-10-26 09:55:08]'::tsrange ORDER BY i; + i +-------------------------- + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Tue Oct 26 09:55:08 2004 +(3 rows) + +SELECT * FROM test_timestamp WHERE i <@ '[2004-10-26 04:55:08, 2004-10-26 09:55:08)'::tsrange ORDER BY i; + i +-------------------------- + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 +(3 rows) + +SELECT * FROM test_timestamp WHERE i <@ '(2004-10-26 04:55:08, 2004-10-26 09:55:08)'::tsrange ORDER BY i; + i +-------------------------- + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 +(2 rows) + +SELECT * FROM test_timestamp WHERE i <@ tsrange('2004-10-26 04:55:08', '2004-10-26 09:55:08') ORDER BY i; + i +-------------------------- + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 +(3 rows) + +SELECT * FROM test_timestamp WHERE i <@ tsrange(NULL, '2004-10-26 09:55:08') ORDER BY i; + i +-------------------------- + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 +(4 rows) + +SELECT * FROM test_timestamp WHERE i <@ tsrange('2004-10-26 04:55:08', NULL) ORDER BY i; + i +-------------------------- + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Tue Oct 26 09:55:08 2004 + Tue Oct 26 10:55:08 2004 +(5 rows) + +SELECT * FROM test_timestamp WHERE i <@ tsrange(NULL, NULL) ORDER BY i; + i +-------------------------- + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 + Tue Oct 26 09:55:08 2004 + Tue Oct 26 10:55:08 2004 +(6 rows) + diff --git a/contrib/btree_gin/expected/timestamptz.out b/contrib/btree_gin/expected/timestamptz.out index d53963d..de2087b 100644 --- a/contrib/btree_gin/expected/timestamptz.out +++ b/contrib/btree_gin/expected/timestamptz.out @@ -49,3 +49,73 @@ SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER Tue Oct 26 10:55:08 2004 PDT (2 rows) +SELECT * FROM test_timestamptz WHERE i <@ '[2004-10-26 04:55:08, 2004-10-26 09:55:08]'::tstzrange ORDER BY i; + i +------------------------------ + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT + Tue Oct 26 09:55:08 2004 PDT +(4 rows) + +SELECT * FROM test_timestamptz WHERE i <@ '(2004-10-26 04:55:08, 2004-10-26 09:55:08]'::tstzrange ORDER BY i; + i +------------------------------ + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT + Tue Oct 26 09:55:08 2004 PDT +(3 rows) + +SELECT * FROM test_timestamptz WHERE i <@ '[2004-10-26 04:55:08, 2004-10-26 09:55:08)'::tstzrange ORDER BY i; + i +------------------------------ + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT +(3 rows) + +SELECT * FROM test_timestamptz WHERE i <@ '(2004-10-26 04:55:08, 2004-10-26 09:55:08)'::tstzrange ORDER BY i; + i +------------------------------ + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT +(2 rows) + +SELECT * FROM test_timestamptz WHERE i <@ tstzrange('2004-10-26 04:55:08', '2004-10-26 09:55:08') ORDER BY i; + i +------------------------------ + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT +(3 rows) + +SELECT * FROM test_timestamptz WHERE i <@ tstzrange(NULL, '2004-10-26 09:55:08') ORDER BY i; + i +------------------------------ + Tue Oct 26 03:55:08 2004 PDT + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT +(4 rows) + +SELECT * FROM test_timestamptz WHERE i <@ tstzrange('2004-10-26 04:55:08', NULL) ORDER BY i; + i +------------------------------ + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT + Tue Oct 26 09:55:08 2004 PDT + Tue Oct 26 10:55:08 2004 PDT +(5 rows) + +SELECT * FROM test_timestamptz WHERE i <@ tstzrange(NULL, NULL) ORDER BY i; + i +------------------------------ + Tue Oct 26 03:55:08 2004 PDT + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT + Tue Oct 26 09:55:08 2004 PDT + Tue Oct 26 10:55:08 2004 PDT +(6 rows) + diff --git a/contrib/btree_gin/sql/date.sql b/contrib/btree_gin/sql/date.sql index 35086f6..79f329a 100644 --- a/contrib/btree_gin/sql/date.sql +++ b/contrib/btree_gin/sql/date.sql @@ -20,3 +20,14 @@ SELECT * FROM test_date WHERE i<='2004-10-26'::date ORDER BY i; SELECT * FROM test_date WHERE i='2004-10-26'::date ORDER BY i; SELECT * FROM test_date WHERE i>='2004-10-26'::date ORDER BY i; SELECT * FROM test_date WHERE i>'2004-10-26'::date ORDER BY i; + +SELECT * FROM test_date WHERE i <@ '[2004-10-24, 2004-10-27]'::daterange ORDER BY i; +SELECT * FROM test_date WHERE i <@ '(2004-10-24, 2004-10-27]'::daterange ORDER BY i; +SELECT * FROM test_date WHERE i <@ '[2004-10-24, 2004-10-27)'::daterange ORDER BY i; +SELECT * FROM test_date WHERE i <@ '(2004-10-24, 2004-10-27)'::daterange ORDER BY i; + +SELECT * FROM test_date WHERE i <@ daterange('2004-10-24', '2004-10-27') ORDER BY i; +SELECT * FROM test_date WHERE i <@ daterange(NULL, '2004-10-27') ORDER BY i; +SELECT * FROM test_date WHERE i <@ daterange('2004-10-24', NULL) ORDER BY i; +SELECT * FROM test_date WHERE i <@ daterange(NULL, NULL) ORDER BY i; + diff --git a/contrib/btree_gin/sql/int4.sql b/contrib/btree_gin/sql/int4.sql index 6499c29..af96aa5 100644 --- a/contrib/btree_gin/sql/int4.sql +++ b/contrib/btree_gin/sql/int4.sql @@ -13,3 +13,14 @@ SELECT * FROM test_int4 WHERE i<=1::int4 ORDER BY i; SELECT * FROM test_int4 WHERE i=1::int4 ORDER BY i; SELECT * FROM test_int4 WHERE i>=1::int4 ORDER BY i; SELECT * FROM test_int4 WHERE i>1::int4 ORDER BY i; + +SELECT * FROM test_int4 WHERE i <@ '[-1, 1]'::int4range ORDER BY i; +SELECT * FROM test_int4 WHERE i <@ '(-1, 1]'::int4range ORDER BY i; +SELECT * FROM test_int4 WHERE i <@ '[-1, 1)'::int4range ORDER BY i; +SELECT * FROM test_int4 WHERE i <@ '(-1, 1)'::int4range ORDER BY i; + +SELECT * FROM test_int4 WHERE i <@ int4range(-1, 1) ORDER BY i; +SELECT * FROM test_int4 WHERE i <@ int4range(NULL, 1) ORDER BY i; +SELECT * FROM test_int4 WHERE i <@ int4range(-1, NULL) ORDER BY i; +SELECT * FROM test_int4 WHERE i <@ int4range(NULL, NULL) ORDER BY i; + diff --git a/contrib/btree_gin/sql/int8.sql b/contrib/btree_gin/sql/int8.sql index 4d9c287..0e3cf5f 100644 --- a/contrib/btree_gin/sql/int8.sql +++ b/contrib/btree_gin/sql/int8.sql @@ -13,3 +13,14 @@ SELECT * FROM test_int8 WHERE i<=1::int8 ORDER BY i; SELECT * FROM test_int8 WHERE i=1::int8 ORDER BY i; SELECT * FROM test_int8 WHERE i>=1::int8 ORDER BY i; SELECT * FROM test_int8 WHERE i>1::int8 ORDER BY i; + +SELECT * FROM test_int8 WHERE i <@ '[-1, 1]'::int8range ORDER BY i; +SELECT * FROM test_int8 WHERE i <@ '(-1, 1]'::int8range ORDER BY i; +SELECT * FROM test_int8 WHERE i <@ '[-1, 1)'::int8range ORDER BY i; +SELECT * FROM test_int8 WHERE i <@ '(-1, 1)'::int8range ORDER BY i; + +SELECT * FROM test_int8 WHERE i <@ int8range(-1, 1) ORDER BY i; +SELECT * FROM test_int8 WHERE i <@ int8range(NULL, 1) ORDER BY i; +SELECT * FROM test_int8 WHERE i <@ int8range(-1, NULL) ORDER BY i; +SELECT * FROM test_int8 WHERE i <@ int8range(NULL, NULL) ORDER BY i; + diff --git a/contrib/btree_gin/sql/numeric.sql b/contrib/btree_gin/sql/numeric.sql index dbaaa2c..6289ccf 100644 --- a/contrib/btree_gin/sql/numeric.sql +++ b/contrib/btree_gin/sql/numeric.sql @@ -13,3 +13,14 @@ SELECT * FROM test_numeric WHERE i<='1'::numeric ORDER BY i; SELECT * FROM test_numeric WHERE i='1'::numeric ORDER BY i; SELECT * FROM test_numeric WHERE i>='1'::numeric ORDER BY i; SELECT * FROM test_numeric WHERE i>'1'::numeric ORDER BY i; + +SELECT * FROM test_numeric WHERE i <@ '[-1, 1]'::numrange ORDER BY i; +SELECT * FROM test_numeric WHERE i <@ '(-1, 1]'::numrange ORDER BY i; +SELECT * FROM test_numeric WHERE i <@ '[-1, 1)'::numrange ORDER BY i; +SELECT * FROM test_numeric WHERE i <@ '(-1, 1)'::numrange ORDER BY i; + +SELECT * FROM test_numeric WHERE i <@ numrange(-1, 1) ORDER BY i; +SELECT * FROM test_numeric WHERE i <@ numrange(NULL, 1) ORDER BY i; +SELECT * FROM test_numeric WHERE i <@ numrange(-1, NULL) ORDER BY i; +SELECT * FROM test_numeric WHERE i <@ numrange(NULL, NULL) ORDER BY i; + diff --git a/contrib/btree_gin/sql/timestamp.sql b/contrib/btree_gin/sql/timestamp.sql index 56727e8..8876257 100644 --- a/contrib/btree_gin/sql/timestamp.sql +++ b/contrib/btree_gin/sql/timestamp.sql @@ -20,3 +20,14 @@ SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i; SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i; SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i; + +SELECT * FROM test_timestamp WHERE i <@ '[2004-10-26 04:55:08, 2004-10-26 09:55:08]'::tsrange ORDER BY i; +SELECT * FROM test_timestamp WHERE i <@ '(2004-10-26 04:55:08, 2004-10-26 09:55:08]'::tsrange ORDER BY i; +SELECT * FROM test_timestamp WHERE i <@ '[2004-10-26 04:55:08, 2004-10-26 09:55:08)'::tsrange ORDER BY i; +SELECT * FROM test_timestamp WHERE i <@ '(2004-10-26 04:55:08, 2004-10-26 09:55:08)'::tsrange ORDER BY i; + +SELECT * FROM test_timestamp WHERE i <@ tsrange('2004-10-26 04:55:08', '2004-10-26 09:55:08') ORDER BY i; +SELECT * FROM test_timestamp WHERE i <@ tsrange(NULL, '2004-10-26 09:55:08') ORDER BY i; +SELECT * FROM test_timestamp WHERE i <@ tsrange('2004-10-26 04:55:08', NULL) ORDER BY i; +SELECT * FROM test_timestamp WHERE i <@ tsrange(NULL, NULL) ORDER BY i; + diff --git a/contrib/btree_gin/sql/timestamptz.sql b/contrib/btree_gin/sql/timestamptz.sql index e6cfdb1..7058e4a 100644 --- a/contrib/btree_gin/sql/timestamptz.sql +++ b/contrib/btree_gin/sql/timestamptz.sql @@ -20,3 +20,14 @@ SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i; SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i; SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i; + +SELECT * FROM test_timestamptz WHERE i <@ '[2004-10-26 04:55:08, 2004-10-26 09:55:08]'::tstzrange ORDER BY i; +SELECT * FROM test_timestamptz WHERE i <@ '(2004-10-26 04:55:08, 2004-10-26 09:55:08]'::tstzrange ORDER BY i; +SELECT * FROM test_timestamptz WHERE i <@ '[2004-10-26 04:55:08, 2004-10-26 09:55:08)'::tstzrange ORDER BY i; +SELECT * FROM test_timestamptz WHERE i <@ '(2004-10-26 04:55:08, 2004-10-26 09:55:08)'::tstzrange ORDER BY i; + +SELECT * FROM test_timestamptz WHERE i <@ tstzrange('2004-10-26 04:55:08', '2004-10-26 09:55:08') ORDER BY i; +SELECT * FROM test_timestamptz WHERE i <@ tstzrange(NULL, '2004-10-26 09:55:08') ORDER BY i; +SELECT * FROM test_timestamptz WHERE i <@ tstzrange('2004-10-26 04:55:08', NULL) ORDER BY i; +SELECT * FROM test_timestamptz WHERE i <@ tstzrange(NULL, NULL) ORDER BY i; + diff --git a/doc/src/sgml/btree-gin.sgml b/doc/src/sgml/btree-gin.sgml index 42b9e97..92b1573 100644 --- a/doc/src/sgml/btree-gin.sgml +++ b/doc/src/sgml/btree-gin.sgml @@ -11,12 +11,19 @@ btree_gin provides sample GIN operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, - float8, timestamp with time zone, + float8, numeric, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, "char", varchar, text, bytea, bit, - varbit, macaddr, inet, and cidr. + varbit, macaddr, inet and cidr. + + + + btree_gin also provides GIN index support for + <@ range operations for types int4, int8, + numeric, timestamp with time zone, + timestamp without time zone and date. @@ -47,7 +54,7 @@ SELECT * FROM test WHERE a < 10; Authors - Teodor Sigaev (teodor@stack.net) and + Teodor Sigaev (teodor@sigaev.ru) and Oleg Bartunov (oleg@sai.msu.su). See for additional information.