1/* contrib/intarray/intarray--1.2.sql */ 2 3-- complain if script is sourced in psql, rather than via CREATE EXTENSION 4\echo Use "CREATE EXTENSION intarray" to load this file. \quit 5 6-- 7-- Create the user-defined type for the 1-D integer arrays (_int4) 8-- 9 10-- Query type 11CREATE FUNCTION bqarr_in(cstring) 12RETURNS query_int 13AS 'MODULE_PATHNAME' 14LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 15 16CREATE FUNCTION bqarr_out(query_int) 17RETURNS cstring 18AS 'MODULE_PATHNAME' 19LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 20 21CREATE TYPE query_int ( 22 INTERNALLENGTH = -1, 23 INPUT = bqarr_in, 24 OUTPUT = bqarr_out 25); 26 27--only for debug 28CREATE FUNCTION querytree(query_int) 29RETURNS text 30AS 'MODULE_PATHNAME' 31LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 32 33 34CREATE FUNCTION boolop(_int4, query_int) 35RETURNS bool 36AS 'MODULE_PATHNAME' 37LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 38 39COMMENT ON FUNCTION boolop(_int4, query_int) IS 'boolean operation with array'; 40 41CREATE FUNCTION rboolop(query_int, _int4) 42RETURNS bool 43AS 'MODULE_PATHNAME' 44LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 45 46COMMENT ON FUNCTION rboolop(query_int, _int4) IS 'boolean operation with array'; 47 48CREATE FUNCTION _int_matchsel(internal, oid, internal, integer) 49RETURNS float8 50AS 'MODULE_PATHNAME' 51LANGUAGE C STRICT STABLE PARALLEL SAFE; 52 53CREATE OPERATOR @@ ( 54 LEFTARG = _int4, 55 RIGHTARG = query_int, 56 PROCEDURE = boolop, 57 COMMUTATOR = '~~', 58 RESTRICT = _int_matchsel, 59 JOIN = contjoinsel 60); 61 62CREATE OPERATOR ~~ ( 63 LEFTARG = query_int, 64 RIGHTARG = _int4, 65 PROCEDURE = rboolop, 66 COMMUTATOR = '@@', 67 RESTRICT = _int_matchsel, 68 JOIN = contjoinsel 69); 70 71 72-- 73-- External C-functions for R-tree methods 74-- 75 76-- Comparison methods 77 78CREATE FUNCTION _int_contains(_int4, _int4) 79RETURNS bool 80AS 'MODULE_PATHNAME' 81LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 82 83COMMENT ON FUNCTION _int_contains(_int4, _int4) IS 'contains'; 84 85CREATE FUNCTION _int_contained(_int4, _int4) 86RETURNS bool 87AS 'MODULE_PATHNAME' 88LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 89 90COMMENT ON FUNCTION _int_contained(_int4, _int4) IS 'contained in'; 91 92CREATE FUNCTION _int_overlap(_int4, _int4) 93RETURNS bool 94AS 'MODULE_PATHNAME' 95LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 96 97COMMENT ON FUNCTION _int_overlap(_int4, _int4) IS 'overlaps'; 98 99CREATE FUNCTION _int_same(_int4, _int4) 100RETURNS bool 101AS 'MODULE_PATHNAME' 102LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 103 104COMMENT ON FUNCTION _int_same(_int4, _int4) IS 'same as'; 105 106CREATE FUNCTION _int_different(_int4, _int4) 107RETURNS bool 108AS 'MODULE_PATHNAME' 109LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 110 111COMMENT ON FUNCTION _int_different(_int4, _int4) IS 'different'; 112 113-- support routines for indexing 114 115CREATE FUNCTION _int_union(_int4, _int4) 116RETURNS _int4 117AS 'MODULE_PATHNAME' 118LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 119 120CREATE FUNCTION _int_inter(_int4, _int4) 121RETURNS _int4 122AS 'MODULE_PATHNAME' 123LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 124 125CREATE FUNCTION _int_overlap_sel(internal, oid, internal, integer) 126RETURNS float8 127AS 'MODULE_PATHNAME' 128LANGUAGE C STRICT STABLE PARALLEL SAFE; 129 130CREATE FUNCTION _int_contains_sel(internal, oid, internal, integer) 131RETURNS float8 132AS 'MODULE_PATHNAME' 133LANGUAGE C STRICT STABLE PARALLEL SAFE; 134 135CREATE FUNCTION _int_contained_sel(internal, oid, internal, integer) 136RETURNS float8 137AS 'MODULE_PATHNAME' 138LANGUAGE C STRICT STABLE PARALLEL SAFE; 139 140CREATE FUNCTION _int_overlap_joinsel(internal, oid, internal, smallint, internal) 141RETURNS float8 142AS 'MODULE_PATHNAME' 143LANGUAGE C STRICT STABLE PARALLEL SAFE; 144 145CREATE FUNCTION _int_contains_joinsel(internal, oid, internal, smallint, internal) 146RETURNS float8 147AS 'MODULE_PATHNAME' 148LANGUAGE C STRICT STABLE PARALLEL SAFE; 149 150CREATE FUNCTION _int_contained_joinsel(internal, oid, internal, smallint, internal) 151RETURNS float8 152AS 'MODULE_PATHNAME' 153LANGUAGE C STRICT STABLE PARALLEL SAFE; 154 155-- 156-- OPERATORS 157-- 158 159CREATE OPERATOR && ( 160 LEFTARG = _int4, 161 RIGHTARG = _int4, 162 PROCEDURE = _int_overlap, 163 COMMUTATOR = '&&', 164 RESTRICT = _int_overlap_sel, 165 JOIN = _int_overlap_joinsel 166); 167 168--CREATE OPERATOR = ( 169-- LEFTARG = _int4, 170-- RIGHTARG = _int4, 171-- PROCEDURE = _int_same, 172-- COMMUTATOR = '=', 173-- NEGATOR = '<>', 174-- RESTRICT = eqsel, 175-- JOIN = eqjoinsel, 176-- SORT1 = '<', 177-- SORT2 = '<' 178--); 179 180--CREATE OPERATOR <> ( 181-- LEFTARG = _int4, 182-- RIGHTARG = _int4, 183-- PROCEDURE = _int_different, 184-- COMMUTATOR = '<>', 185-- NEGATOR = '=', 186-- RESTRICT = neqsel, 187-- JOIN = neqjoinsel 188--); 189 190CREATE OPERATOR @> ( 191 LEFTARG = _int4, 192 RIGHTARG = _int4, 193 PROCEDURE = _int_contains, 194 COMMUTATOR = '<@', 195 RESTRICT = _int_contains_sel, 196 JOIN = _int_contains_joinsel 197); 198 199CREATE OPERATOR <@ ( 200 LEFTARG = _int4, 201 RIGHTARG = _int4, 202 PROCEDURE = _int_contained, 203 COMMUTATOR = '@>', 204 RESTRICT = _int_contained_sel, 205 JOIN = _int_contained_joinsel 206); 207 208-- obsolete: 209CREATE OPERATOR @ ( 210 LEFTARG = _int4, 211 RIGHTARG = _int4, 212 PROCEDURE = _int_contains, 213 COMMUTATOR = '~', 214 RESTRICT = _int_contains_sel, 215 JOIN = _int_contains_joinsel 216); 217 218CREATE OPERATOR ~ ( 219 LEFTARG = _int4, 220 RIGHTARG = _int4, 221 PROCEDURE = _int_contained, 222 COMMUTATOR = '@', 223 RESTRICT = _int_contained_sel, 224 JOIN = _int_contained_joinsel 225); 226 227-------------- 228CREATE FUNCTION intset(int4) 229RETURNS _int4 230AS 'MODULE_PATHNAME' 231LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 232 233CREATE FUNCTION icount(_int4) 234RETURNS int4 235AS 'MODULE_PATHNAME' 236LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 237 238CREATE OPERATOR # ( 239 RIGHTARG = _int4, 240 PROCEDURE = icount 241); 242 243CREATE FUNCTION sort(_int4, text) 244RETURNS _int4 245AS 'MODULE_PATHNAME' 246LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 247 248CREATE FUNCTION sort(_int4) 249RETURNS _int4 250AS 'MODULE_PATHNAME' 251LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 252 253CREATE FUNCTION sort_asc(_int4) 254RETURNS _int4 255AS 'MODULE_PATHNAME' 256LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 257 258CREATE FUNCTION sort_desc(_int4) 259RETURNS _int4 260AS 'MODULE_PATHNAME' 261LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 262 263CREATE FUNCTION uniq(_int4) 264RETURNS _int4 265AS 'MODULE_PATHNAME' 266LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 267 268CREATE FUNCTION idx(_int4, int4) 269RETURNS int4 270AS 'MODULE_PATHNAME' 271LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 272 273CREATE OPERATOR # ( 274 LEFTARG = _int4, 275 RIGHTARG = int4, 276 PROCEDURE = idx 277); 278 279CREATE FUNCTION subarray(_int4, int4, int4) 280RETURNS _int4 281AS 'MODULE_PATHNAME' 282LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 283 284CREATE FUNCTION subarray(_int4, int4) 285RETURNS _int4 286AS 'MODULE_PATHNAME' 287LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 288 289CREATE FUNCTION intarray_push_elem(_int4, int4) 290RETURNS _int4 291AS 'MODULE_PATHNAME' 292LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 293 294CREATE OPERATOR + ( 295 LEFTARG = _int4, 296 RIGHTARG = int4, 297 PROCEDURE = intarray_push_elem 298); 299 300CREATE FUNCTION intarray_push_array(_int4, _int4) 301RETURNS _int4 302AS 'MODULE_PATHNAME' 303LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 304 305CREATE OPERATOR + ( 306 LEFTARG = _int4, 307 RIGHTARG = _int4, 308 COMMUTATOR = +, 309 PROCEDURE = intarray_push_array 310); 311 312CREATE FUNCTION intarray_del_elem(_int4, int4) 313RETURNS _int4 314AS 'MODULE_PATHNAME' 315LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 316 317CREATE OPERATOR - ( 318 LEFTARG = _int4, 319 RIGHTARG = int4, 320 PROCEDURE = intarray_del_elem 321); 322 323CREATE FUNCTION intset_union_elem(_int4, int4) 324RETURNS _int4 325AS 'MODULE_PATHNAME' 326LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 327 328CREATE OPERATOR | ( 329 LEFTARG = _int4, 330 RIGHTARG = int4, 331 PROCEDURE = intset_union_elem 332); 333 334CREATE OPERATOR | ( 335 LEFTARG = _int4, 336 RIGHTARG = _int4, 337 COMMUTATOR = |, 338 PROCEDURE = _int_union 339); 340 341CREATE FUNCTION intset_subtract(_int4, _int4) 342RETURNS _int4 343AS 'MODULE_PATHNAME' 344LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 345 346CREATE OPERATOR - ( 347 LEFTARG = _int4, 348 RIGHTARG = _int4, 349 PROCEDURE = intset_subtract 350); 351 352CREATE OPERATOR & ( 353 LEFTARG = _int4, 354 RIGHTARG = _int4, 355 COMMUTATOR = &, 356 PROCEDURE = _int_inter 357); 358-------------- 359 360-- define the GiST support methods 361CREATE FUNCTION g_int_consistent(internal,_int4,smallint,oid,internal) 362RETURNS bool 363AS 'MODULE_PATHNAME' 364LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 365 366CREATE FUNCTION g_int_compress(internal) 367RETURNS internal 368AS 'MODULE_PATHNAME' 369LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 370 371CREATE FUNCTION g_int_decompress(internal) 372RETURNS internal 373AS 'MODULE_PATHNAME' 374LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 375 376CREATE FUNCTION g_int_penalty(internal,internal,internal) 377RETURNS internal 378AS 'MODULE_PATHNAME' 379LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 380 381CREATE FUNCTION g_int_picksplit(internal, internal) 382RETURNS internal 383AS 'MODULE_PATHNAME' 384LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 385 386CREATE FUNCTION g_int_union(internal, internal) 387RETURNS _int4 388AS 'MODULE_PATHNAME' 389LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 390 391CREATE FUNCTION g_int_same(_int4, _int4, internal) 392RETURNS internal 393AS 'MODULE_PATHNAME' 394LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 395 396 397-- Create the operator class for indexing 398 399CREATE OPERATOR CLASS gist__int_ops 400DEFAULT FOR TYPE _int4 USING gist AS 401 OPERATOR 3 &&, 402 OPERATOR 6 = (anyarray, anyarray), 403 OPERATOR 7 @>, 404 OPERATOR 8 <@, 405 OPERATOR 13 @, 406 OPERATOR 14 ~, 407 OPERATOR 20 @@ (_int4, query_int), 408 FUNCTION 1 g_int_consistent (internal, _int4, smallint, oid, internal), 409 FUNCTION 2 g_int_union (internal, internal), 410 FUNCTION 3 g_int_compress (internal), 411 FUNCTION 4 g_int_decompress (internal), 412 FUNCTION 5 g_int_penalty (internal, internal, internal), 413 FUNCTION 6 g_int_picksplit (internal, internal), 414 FUNCTION 7 g_int_same (_int4, _int4, internal); 415 416 417--------------------------------------------- 418-- intbig 419--------------------------------------------- 420-- define the GiST support methods 421 422CREATE FUNCTION _intbig_in(cstring) 423RETURNS intbig_gkey 424AS 'MODULE_PATHNAME' 425LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 426 427CREATE FUNCTION _intbig_out(intbig_gkey) 428RETURNS cstring 429AS 'MODULE_PATHNAME' 430LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 431 432CREATE TYPE intbig_gkey ( 433 INTERNALLENGTH = -1, 434 INPUT = _intbig_in, 435 OUTPUT = _intbig_out 436); 437 438CREATE FUNCTION g_intbig_consistent(internal,_int4,smallint,oid,internal) 439RETURNS bool 440AS 'MODULE_PATHNAME' 441LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 442 443CREATE FUNCTION g_intbig_compress(internal) 444RETURNS internal 445AS 'MODULE_PATHNAME' 446LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 447 448CREATE FUNCTION g_intbig_decompress(internal) 449RETURNS internal 450AS 'MODULE_PATHNAME' 451LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 452 453CREATE FUNCTION g_intbig_penalty(internal,internal,internal) 454RETURNS internal 455AS 'MODULE_PATHNAME' 456LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 457 458CREATE FUNCTION g_intbig_picksplit(internal, internal) 459RETURNS internal 460AS 'MODULE_PATHNAME' 461LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 462 463CREATE FUNCTION g_intbig_union(internal, internal) 464RETURNS intbig_gkey 465AS 'MODULE_PATHNAME' 466LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 467 468CREATE FUNCTION g_intbig_same(intbig_gkey, intbig_gkey, internal) 469RETURNS internal 470AS 'MODULE_PATHNAME' 471LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 472 473-- register the opclass for indexing (not as default) 474 475CREATE OPERATOR CLASS gist__intbig_ops 476FOR TYPE _int4 USING gist 477AS 478 OPERATOR 3 &&, 479 OPERATOR 6 = (anyarray, anyarray), 480 OPERATOR 7 @>, 481 OPERATOR 8 <@, 482 OPERATOR 13 @, 483 OPERATOR 14 ~, 484 OPERATOR 20 @@ (_int4, query_int), 485 FUNCTION 1 g_intbig_consistent (internal, _int4, smallint, oid, internal), 486 FUNCTION 2 g_intbig_union (internal, internal), 487 FUNCTION 3 g_intbig_compress (internal), 488 FUNCTION 4 g_intbig_decompress (internal), 489 FUNCTION 5 g_intbig_penalty (internal, internal, internal), 490 FUNCTION 6 g_intbig_picksplit (internal, internal), 491 FUNCTION 7 g_intbig_same (intbig_gkey, intbig_gkey, internal), 492 STORAGE intbig_gkey; 493 494--GIN 495 496CREATE FUNCTION ginint4_queryextract(_int4, internal, int2, internal, internal, internal, internal) 497RETURNS internal 498AS 'MODULE_PATHNAME' 499LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 500 501CREATE FUNCTION ginint4_consistent(internal, int2, _int4, int4, internal, internal, internal, internal) 502RETURNS bool 503AS 'MODULE_PATHNAME' 504LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 505 506CREATE OPERATOR CLASS gin__int_ops 507FOR TYPE _int4 USING gin 508AS 509 OPERATOR 3 &&, 510 OPERATOR 6 = (anyarray, anyarray), 511 OPERATOR 7 @>, 512 OPERATOR 8 <@, 513 OPERATOR 13 @, 514 OPERATOR 14 ~, 515 OPERATOR 20 @@ (_int4, query_int), 516 FUNCTION 1 btint4cmp (int4, int4), 517 FUNCTION 2 ginarrayextract (anyarray, internal, internal), 518 FUNCTION 3 ginint4_queryextract (_int4, internal, int2, internal, internal, internal, internal), 519 FUNCTION 4 ginint4_consistent (internal, int2, _int4, int4, internal, internal, internal, internal), 520 STORAGE int4; 521