codekingpro/portable-devtools

1/* contrib/cube/cube--1.2.sql */2 3-- complain if script is sourced in psql, rather than via CREATE EXTENSION4\echo Use "CREATE EXTENSION cube" to load this file. \quit5 6-- Create the user-defined type for N-dimensional boxes7 8CREATE FUNCTION cube_in(cstring)9RETURNS cube10AS 'MODULE_PATHNAME'11LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;12 13CREATE FUNCTION cube(float8[], float8[]) RETURNS cube14AS 'MODULE_PATHNAME', 'cube_a_f8_f8'15LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;16 17CREATE FUNCTION cube(float8[]) RETURNS cube18AS 'MODULE_PATHNAME', 'cube_a_f8'19LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;20 21CREATE FUNCTION cube_out(cube)22RETURNS cstring23AS 'MODULE_PATHNAME'24LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;25 26CREATE TYPE cube (27	INTERNALLENGTH = variable,28	INPUT = cube_in,29	OUTPUT = cube_out,30	ALIGNMENT = double31);32 33COMMENT ON TYPE cube IS 'multi-dimensional cube ''(FLOAT-1, FLOAT-2, ..., FLOAT-N), (FLOAT-1, FLOAT-2, ..., FLOAT-N)''';34 35--36-- External C-functions for R-tree methods37--38 39-- Comparison methods40 41CREATE FUNCTION cube_eq(cube, cube)42RETURNS bool43AS 'MODULE_PATHNAME'44LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;45 46COMMENT ON FUNCTION cube_eq(cube, cube) IS 'same as';47 48CREATE FUNCTION cube_ne(cube, cube)49RETURNS bool50AS 'MODULE_PATHNAME'51LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;52 53COMMENT ON FUNCTION cube_ne(cube, cube) IS 'different';54 55CREATE FUNCTION cube_lt(cube, cube)56RETURNS bool57AS 'MODULE_PATHNAME'58LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;59 60COMMENT ON FUNCTION cube_lt(cube, cube) IS 'lower than';61 62CREATE FUNCTION cube_gt(cube, cube)63RETURNS bool64AS 'MODULE_PATHNAME'65LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;66 67COMMENT ON FUNCTION cube_gt(cube, cube) IS 'greater than';68 69CREATE FUNCTION cube_le(cube, cube)70RETURNS bool71AS 'MODULE_PATHNAME'72LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;73 74COMMENT ON FUNCTION cube_le(cube, cube) IS 'lower than or equal to';75 76CREATE FUNCTION cube_ge(cube, cube)77RETURNS bool78AS 'MODULE_PATHNAME'79LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;80 81COMMENT ON FUNCTION cube_ge(cube, cube) IS 'greater than or equal to';82 83CREATE FUNCTION cube_cmp(cube, cube)84RETURNS int485AS 'MODULE_PATHNAME'86LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;87 88COMMENT ON FUNCTION cube_cmp(cube, cube) IS 'btree comparison function';89 90CREATE FUNCTION cube_contains(cube, cube)91RETURNS bool92AS 'MODULE_PATHNAME'93LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;94 95COMMENT ON FUNCTION cube_contains(cube, cube) IS 'contains';96 97CREATE FUNCTION cube_contained(cube, cube)98RETURNS bool99AS 'MODULE_PATHNAME'100LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;101 102COMMENT ON FUNCTION cube_contained(cube, cube) IS 'contained in';103 104CREATE FUNCTION cube_overlap(cube, cube)105RETURNS bool106AS 'MODULE_PATHNAME'107LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;108 109COMMENT ON FUNCTION cube_overlap(cube, cube) IS 'overlaps';110 111-- support routines for indexing112 113CREATE FUNCTION cube_union(cube, cube)114RETURNS cube115AS 'MODULE_PATHNAME'116LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;117 118CREATE FUNCTION cube_inter(cube, cube)119RETURNS cube120AS 'MODULE_PATHNAME'121LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;122 123CREATE FUNCTION cube_size(cube)124RETURNS float8125AS 'MODULE_PATHNAME'126LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;127 128 129-- Misc N-dimensional functions130 131CREATE FUNCTION cube_subset(cube, int4[])132RETURNS cube133AS 'MODULE_PATHNAME'134LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;135 136-- proximity routines137 138CREATE FUNCTION cube_distance(cube, cube)139RETURNS float8140AS 'MODULE_PATHNAME'141LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;142 143CREATE FUNCTION distance_chebyshev(cube, cube)144RETURNS float8145AS 'MODULE_PATHNAME'146LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;147 148CREATE FUNCTION distance_taxicab(cube, cube)149RETURNS float8150AS 'MODULE_PATHNAME'151LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;152 153-- Extracting elements functions154 155CREATE FUNCTION cube_dim(cube)156RETURNS int4157AS 'MODULE_PATHNAME'158LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;159 160CREATE FUNCTION cube_ll_coord(cube, int4)161RETURNS float8162AS 'MODULE_PATHNAME'163LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;164 165CREATE FUNCTION cube_ur_coord(cube, int4)166RETURNS float8167AS 'MODULE_PATHNAME'168LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;169 170CREATE FUNCTION cube_coord(cube, int4)171RETURNS float8172AS 'MODULE_PATHNAME'173LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;174 175CREATE FUNCTION cube_coord_llur(cube, int4)176RETURNS float8177AS 'MODULE_PATHNAME'178LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;179 180CREATE FUNCTION cube(float8) RETURNS cube181AS 'MODULE_PATHNAME', 'cube_f8'182LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;183 184CREATE FUNCTION cube(float8, float8) RETURNS cube185AS 'MODULE_PATHNAME', 'cube_f8_f8'186LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;187 188CREATE FUNCTION cube(cube, float8) RETURNS cube189AS 'MODULE_PATHNAME', 'cube_c_f8'190LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;191 192CREATE FUNCTION cube(cube, float8, float8) RETURNS cube193AS 'MODULE_PATHNAME', 'cube_c_f8_f8'194LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;195 196-- Test if cube is also a point197 198CREATE FUNCTION cube_is_point(cube)199RETURNS bool200AS 'MODULE_PATHNAME'201LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;202 203-- Increasing the size of a cube by a radius in at least n dimensions204 205CREATE FUNCTION cube_enlarge(cube, float8, int4)206RETURNS cube207AS 'MODULE_PATHNAME'208LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;209 210--211-- OPERATORS212--213 214CREATE OPERATOR < (215	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_lt,216	COMMUTATOR = '>', NEGATOR = '>=',217	RESTRICT = scalarltsel, JOIN = scalarltjoinsel218);219 220CREATE OPERATOR > (221	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_gt,222	COMMUTATOR = '<', NEGATOR = '<=',223	RESTRICT = scalargtsel, JOIN = scalargtjoinsel224);225 226CREATE OPERATOR <= (227	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_le,228	COMMUTATOR = '>=', NEGATOR = '>',229	RESTRICT = scalarltsel, JOIN = scalarltjoinsel230);231 232CREATE OPERATOR >= (233	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_ge,234	COMMUTATOR = '<=', NEGATOR = '<',235	RESTRICT = scalargtsel, JOIN = scalargtjoinsel236);237 238CREATE OPERATOR && (239	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_overlap,240	COMMUTATOR = '&&',241	RESTRICT = areasel, JOIN = areajoinsel242);243 244CREATE OPERATOR = (245	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_eq,246	COMMUTATOR = '=', NEGATOR = '<>',247	RESTRICT = eqsel, JOIN = eqjoinsel,248	MERGES249);250 251CREATE OPERATOR <> (252	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_ne,253	COMMUTATOR = '<>', NEGATOR = '=',254	RESTRICT = neqsel, JOIN = neqjoinsel255);256 257CREATE OPERATOR @> (258	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_contains,259	COMMUTATOR = '<@',260	RESTRICT = contsel, JOIN = contjoinsel261);262 263CREATE OPERATOR <@ (264	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_contained,265	COMMUTATOR = '@>',266	RESTRICT = contsel, JOIN = contjoinsel267);268 269CREATE OPERATOR -> (270	LEFTARG = cube, RIGHTARG = int, PROCEDURE = cube_coord271);272 273CREATE OPERATOR ~> (274	LEFTARG = cube, RIGHTARG = int, PROCEDURE = cube_coord_llur275);276 277CREATE OPERATOR <#> (278	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = distance_taxicab,279	COMMUTATOR = '<#>'280);281 282CREATE OPERATOR <-> (283	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_distance,284	COMMUTATOR = '<->'285);286 287CREATE OPERATOR <=> (288	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = distance_chebyshev,289	COMMUTATOR = '<=>'290);291 292-- these are obsolete/deprecated:293CREATE OPERATOR @ (294	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_contains,295	COMMUTATOR = '~',296	RESTRICT = contsel, JOIN = contjoinsel297);298 299CREATE OPERATOR ~ (300	LEFTARG = cube, RIGHTARG = cube, PROCEDURE = cube_contained,301	COMMUTATOR = '@',302	RESTRICT = contsel, JOIN = contjoinsel303);304 305 306-- define the GiST support methods307CREATE FUNCTION g_cube_consistent(internal,cube,smallint,oid,internal)308RETURNS bool309AS 'MODULE_PATHNAME'310LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;311 312CREATE FUNCTION g_cube_compress(internal)313RETURNS internal314AS 'MODULE_PATHNAME'315LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;316 317CREATE FUNCTION g_cube_decompress(internal)318RETURNS internal319AS 'MODULE_PATHNAME'320LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;321 322CREATE FUNCTION g_cube_penalty(internal,internal,internal)323RETURNS internal324AS 'MODULE_PATHNAME'325LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;326 327CREATE FUNCTION g_cube_picksplit(internal, internal)328RETURNS internal329AS 'MODULE_PATHNAME'330LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;331 332CREATE FUNCTION g_cube_union(internal, internal)333RETURNS cube334AS 'MODULE_PATHNAME'335LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;336 337CREATE FUNCTION g_cube_same(cube, cube, internal)338RETURNS internal339AS 'MODULE_PATHNAME'340LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;341 342CREATE FUNCTION g_cube_distance (internal, cube, smallint, oid, internal)343RETURNS float8344AS 'MODULE_PATHNAME'345LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;346 347-- Create the operator classes for indexing348 349CREATE OPERATOR CLASS cube_ops350    DEFAULT FOR TYPE cube USING btree AS351        OPERATOR        1       < ,352        OPERATOR        2       <= ,353        OPERATOR        3       = ,354        OPERATOR        4       >= ,355        OPERATOR        5       > ,356        FUNCTION        1       cube_cmp(cube, cube);357 358CREATE OPERATOR CLASS gist_cube_ops359    DEFAULT FOR TYPE cube USING gist AS360	OPERATOR	3	&& ,361	OPERATOR	6	= ,362	OPERATOR	7	@> ,363	OPERATOR	8	<@ ,364	OPERATOR	13	@ ,365	OPERATOR	14	~ ,366	OPERATOR	15	~> (cube, int) FOR ORDER BY float_ops,367	OPERATOR	16	<#> (cube, cube) FOR ORDER BY float_ops,368	OPERATOR	17	<-> (cube, cube) FOR ORDER BY float_ops,369	OPERATOR	18	<=> (cube, cube) FOR ORDER BY float_ops,370 371	FUNCTION	1	g_cube_consistent (internal, cube, smallint, oid, internal),372	FUNCTION	2	g_cube_union (internal, internal),373	FUNCTION	3	g_cube_compress (internal),374	FUNCTION	4	g_cube_decompress (internal),375	FUNCTION	5	g_cube_penalty (internal, internal, internal),376	FUNCTION	6	g_cube_picksplit (internal, internal),377	FUNCTION	7	g_cube_same (cube, cube, internal),378	FUNCTION	8	g_cube_distance (internal, cube, smallint, oid, internal);379