diff options
Diffstat (limited to 'contrib/btree_gist/btree_gist--1.0.sql')
| -rw-r--r-- | contrib/btree_gist/btree_gist--1.0.sql | 253 |
1 files changed, 241 insertions, 12 deletions
diff --git a/contrib/btree_gist/btree_gist--1.0.sql b/contrib/btree_gist/btree_gist--1.0.sql index 1ea5fa2db41..dd428995c18 100644 --- a/contrib/btree_gist/btree_gist--1.0.sql +++ b/contrib/btree_gist/btree_gist--1.0.sql @@ -81,6 +81,151 @@ CREATE TYPE gbtreekey_var ( STORAGE = EXTENDED ); +--distance operators + +CREATE FUNCTION cash_dist(money, money) +RETURNS money +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = money, + RIGHTARG = money, + PROCEDURE = cash_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION date_dist(date, date) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = date, + RIGHTARG = date, + PROCEDURE = date_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION float4_dist(float4, float4) +RETURNS float4 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = float4, + RIGHTARG = float4, + PROCEDURE = float4_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION float8_dist(float8, float8) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = float8, + RIGHTARG = float8, + PROCEDURE = float8_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION int2_dist(int2, int2) +RETURNS int2 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = int2, + RIGHTARG = int2, + PROCEDURE = int2_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION int4_dist(int4, int4) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = int4, + RIGHTARG = int4, + PROCEDURE = int4_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION int8_dist(int8, int8) +RETURNS int8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = int8, + RIGHTARG = int8, + PROCEDURE = int8_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION interval_dist(interval, interval) +RETURNS interval +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = interval, + RIGHTARG = interval, + PROCEDURE = interval_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION oid_dist(oid, oid) +RETURNS oid +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = oid, + RIGHTARG = oid, + PROCEDURE = oid_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION time_dist(time, time) +RETURNS interval +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = time, + RIGHTARG = time, + PROCEDURE = time_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION ts_dist(timestamp, timestamp) +RETURNS interval +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = timestamp, + RIGHTARG = timestamp, + PROCEDURE = ts_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION tstz_dist(timestamptz, timestamptz) +RETURNS interval +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE OPERATOR <-> ( + LEFTARG = timestamptz, + RIGHTARG = timestamptz, + PROCEDURE = tstz_dist, + COMMUTATOR = '<->' +); -- @@ -96,6 +241,11 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_oid_distance(internal,oid,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_oid_compress(internal) RETURNS internal AS 'MODULE_PATHNAME' @@ -154,7 +304,9 @@ AS -- that's the only state that can be reproduced during an upgrade from 9.0. ALTER OPERATOR FAMILY gist_oid_ops USING gist ADD - OPERATOR 6 <> (oid, oid) ; + OPERATOR 6 <> (oid, oid) , + OPERATOR 15 <-> (oid, oid) FOR ORDER BY pg_catalog.oid_ops , + FUNCTION 8 (oid, oid) gbt_oid_distance (internal, oid, int2, oid) ; -- @@ -170,6 +322,11 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_int2_distance(internal,int2,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_int2_compress(internal) RETURNS internal AS 'MODULE_PATHNAME' @@ -214,7 +371,9 @@ AS STORAGE gbtreekey4; ALTER OPERATOR FAMILY gist_int2_ops USING gist ADD - OPERATOR 6 <> (int2, int2) ; + OPERATOR 6 <> (int2, int2) , + OPERATOR 15 <-> (int2, int2) FOR ORDER BY pg_catalog.integer_ops , + FUNCTION 8 (int2, int2) gbt_int2_distance (internal, int2, int2, oid) ; -- @@ -230,6 +389,11 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_int4_distance(internal,int4,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_int4_compress(internal) RETURNS internal AS 'MODULE_PATHNAME' @@ -274,7 +438,9 @@ AS STORAGE gbtreekey8; ALTER OPERATOR FAMILY gist_int4_ops USING gist ADD - OPERATOR 6 <> (int4, int4) ; + OPERATOR 6 <> (int4, int4) , + OPERATOR 15 <-> (int4, int4) FOR ORDER BY pg_catalog.integer_ops , + FUNCTION 8 (int4, int4) gbt_int4_distance (internal, int4, int2, oid) ; -- @@ -290,6 +456,11 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_int8_distance(internal,int8,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_int8_compress(internal) RETURNS internal AS 'MODULE_PATHNAME' @@ -334,7 +505,9 @@ AS STORAGE gbtreekey16; ALTER OPERATOR FAMILY gist_int8_ops USING gist ADD - OPERATOR 6 <> (int8, int8) ; + OPERATOR 6 <> (int8, int8) , + OPERATOR 15 <-> (int8, int8) FOR ORDER BY pg_catalog.integer_ops , + FUNCTION 8 (int8, int8) gbt_int8_distance (internal, int8, int2, oid) ; -- @@ -350,6 +523,11 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_float4_distance(internal,float4,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_float4_compress(internal) RETURNS internal AS 'MODULE_PATHNAME' @@ -394,7 +572,9 @@ AS STORAGE gbtreekey8; ALTER OPERATOR FAMILY gist_float4_ops USING gist ADD - OPERATOR 6 <> (float4, float4) ; + OPERATOR 6 <> (float4, float4) , + OPERATOR 15 <-> (float4, float4) FOR ORDER BY pg_catalog.float_ops , + FUNCTION 8 (float4, float4) gbt_float4_distance (internal, float4, int2, oid) ; -- @@ -410,6 +590,11 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_float8_distance(internal,float8,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_float8_compress(internal) RETURNS internal AS 'MODULE_PATHNAME' @@ -454,7 +639,9 @@ AS STORAGE gbtreekey16; ALTER OPERATOR FAMILY gist_float8_ops USING gist ADD - OPERATOR 6 <> (float8, float8) ; + OPERATOR 6 <> (float8, float8) , + OPERATOR 15 <-> (float8, float8) FOR ORDER BY pg_catalog.float_ops , + FUNCTION 8 (float8, float8) gbt_float8_distance (internal, float8, int2, oid) ; -- @@ -470,11 +657,21 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_ts_distance(internal,timestamp,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_tstz_consistent(internal,timestamptz,int2,oid,internal) RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_tstz_distance(internal,timestamptz,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_ts_compress(internal) RETURNS internal AS 'MODULE_PATHNAME' @@ -524,7 +721,9 @@ AS STORAGE gbtreekey16; ALTER OPERATOR FAMILY gist_timestamp_ops USING gist ADD - OPERATOR 6 <> (timestamp, timestamp) ; + OPERATOR 6 <> (timestamp, timestamp) , + OPERATOR 15 <-> (timestamp, timestamp) FOR ORDER BY pg_catalog.interval_ops , + FUNCTION 8 (timestamp, timestamp) gbt_ts_distance (internal, timestamp, int2, oid) ; -- Create the operator class @@ -546,7 +745,9 @@ AS STORAGE gbtreekey16; ALTER OPERATOR FAMILY gist_timestamptz_ops USING gist ADD - OPERATOR 6 <> (timestamptz, timestamptz) ; + OPERATOR 6 <> (timestamptz, timestamptz) , + OPERATOR 15 <-> (timestamptz, timestamptz) FOR ORDER BY pg_catalog.interval_ops , + FUNCTION 8 (timestamptz, timestamptz) gbt_tstz_distance (internal, timestamptz, int2, oid) ; -- @@ -562,6 +763,11 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_time_distance(internal,time,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_timetz_consistent(internal,timetz,int2,oid,internal) RETURNS bool AS 'MODULE_PATHNAME' @@ -616,7 +822,9 @@ AS STORAGE gbtreekey16; ALTER OPERATOR FAMILY gist_time_ops USING gist ADD - OPERATOR 6 <> (time, time) ; + OPERATOR 6 <> (time, time) , + OPERATOR 15 <-> (time, time) FOR ORDER BY pg_catalog.interval_ops , + FUNCTION 8 (time, time) gbt_time_distance (internal, time, int2, oid) ; CREATE OPERATOR CLASS gist_timetz_ops @@ -653,6 +861,11 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_date_distance(internal,date,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_date_compress(internal) RETURNS internal AS 'MODULE_PATHNAME' @@ -697,7 +910,9 @@ AS STORAGE gbtreekey8; ALTER OPERATOR FAMILY gist_date_ops USING gist ADD - OPERATOR 6 <> (date, date) ; + OPERATOR 6 <> (date, date) , + OPERATOR 15 <-> (date, date) FOR ORDER BY pg_catalog.integer_ops , + FUNCTION 8 (date, date) gbt_date_distance (internal, date, int2, oid) ; -- @@ -713,6 +928,11 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_intv_distance(internal,interval,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_intv_compress(internal) RETURNS internal AS 'MODULE_PATHNAME' @@ -762,7 +982,9 @@ AS STORAGE gbtreekey32; ALTER OPERATOR FAMILY gist_interval_ops USING gist ADD - OPERATOR 6 <> (interval, interval) ; + OPERATOR 6 <> (interval, interval) , + OPERATOR 15 <-> (interval, interval) FOR ORDER BY pg_catalog.interval_ops , + FUNCTION 8 (interval, interval) gbt_intv_distance (internal, interval, int2, oid) ; -- @@ -778,6 +1000,11 @@ RETURNS bool AS 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT; +CREATE FUNCTION gbt_cash_distance(internal,money,int2,oid) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + CREATE FUNCTION gbt_cash_compress(internal) RETURNS internal AS 'MODULE_PATHNAME' @@ -822,7 +1049,9 @@ AS STORAGE gbtreekey16; ALTER OPERATOR FAMILY gist_cash_ops USING gist ADD - OPERATOR 6 <> (money, money) ; + OPERATOR 6 <> (money, money) , + OPERATOR 15 <-> (money, money) FOR ORDER BY pg_catalog.money_ops , + FUNCTION 8 (money, money) gbt_cash_distance (internal, money, int2, oid) ; -- |