codekingpro/portable-devtools

1/* contrib/intarray/intarray--1.2.sql */2 3-- complain if script is sourced in psql, rather than via CREATE EXTENSION4\echo Use "CREATE EXTENSION intarray" to load this file. \quit5 6--7-- Create the user-defined type for the 1-D integer arrays (_int4)8--9 10-- Query type11CREATE FUNCTION bqarr_in(cstring)12RETURNS query_int13AS 'MODULE_PATHNAME'14LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;15 16CREATE FUNCTION bqarr_out(query_int)17RETURNS cstring18AS 'MODULE_PATHNAME'19LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;20 21CREATE TYPE query_int (22	INTERNALLENGTH = -1,23	INPUT = bqarr_in,24	OUTPUT = bqarr_out25);26 27--only for debug28CREATE FUNCTION querytree(query_int)29RETURNS text30AS 'MODULE_PATHNAME'31LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;32 33 34CREATE FUNCTION boolop(_int4, query_int)35RETURNS bool36AS '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 bool43AS '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 float850AS '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 = contjoinsel60);61 62CREATE OPERATOR ~~ (63	LEFTARG = query_int,64	RIGHTARG = _int4,65	PROCEDURE = rboolop,66	COMMUTATOR = '@@',67	RESTRICT = _int_matchsel,68	JOIN = contjoinsel69);70 71 72--73-- External C-functions for R-tree methods74--75 76-- Comparison methods77 78CREATE FUNCTION _int_contains(_int4, _int4)79RETURNS bool80AS '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 bool87AS '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 bool94AS '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 bool101AS '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 bool108AS 'MODULE_PATHNAME'109LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;110 111COMMENT ON FUNCTION _int_different(_int4, _int4) IS 'different';112 113-- support routines for indexing114 115CREATE FUNCTION _int_union(_int4, _int4)116RETURNS _int4117AS 'MODULE_PATHNAME'118LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;119 120CREATE FUNCTION _int_inter(_int4, _int4)121RETURNS _int4122AS 'MODULE_PATHNAME'123LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;124 125CREATE FUNCTION _int_overlap_sel(internal, oid, internal, integer)126RETURNS float8127AS 'MODULE_PATHNAME'128LANGUAGE C STRICT STABLE PARALLEL SAFE;129 130CREATE FUNCTION _int_contains_sel(internal, oid, internal, integer)131RETURNS float8132AS 'MODULE_PATHNAME'133LANGUAGE C STRICT STABLE PARALLEL SAFE;134 135CREATE FUNCTION _int_contained_sel(internal, oid, internal, integer)136RETURNS float8137AS 'MODULE_PATHNAME'138LANGUAGE C STRICT STABLE PARALLEL SAFE;139 140CREATE FUNCTION _int_overlap_joinsel(internal, oid, internal, smallint, internal)141RETURNS float8142AS 'MODULE_PATHNAME'143LANGUAGE C STRICT STABLE PARALLEL SAFE;144 145CREATE FUNCTION _int_contains_joinsel(internal, oid, internal, smallint, internal)146RETURNS float8147AS 'MODULE_PATHNAME'148LANGUAGE C STRICT STABLE PARALLEL SAFE;149 150CREATE FUNCTION _int_contained_joinsel(internal, oid, internal, smallint, internal)151RETURNS float8152AS 'MODULE_PATHNAME'153LANGUAGE C STRICT STABLE PARALLEL SAFE;154 155--156-- OPERATORS157--158 159CREATE OPERATOR && (160	LEFTARG = _int4,161	RIGHTARG = _int4,162	PROCEDURE = _int_overlap,163	COMMUTATOR = '&&',164	RESTRICT = _int_overlap_sel,165	JOIN = _int_overlap_joinsel166);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 = neqjoinsel188--);189 190CREATE OPERATOR @> (191	LEFTARG = _int4,192	RIGHTARG = _int4,193	PROCEDURE = _int_contains,194	COMMUTATOR = '<@',195	RESTRICT = _int_contains_sel,196	JOIN = _int_contains_joinsel197);198 199CREATE OPERATOR <@ (200	LEFTARG = _int4,201	RIGHTARG = _int4,202	PROCEDURE = _int_contained,203	COMMUTATOR = '@>',204	RESTRICT = _int_contained_sel,205	JOIN = _int_contained_joinsel206);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_joinsel216);217 218CREATE OPERATOR ~ (219	LEFTARG = _int4,220	RIGHTARG = _int4,221	PROCEDURE = _int_contained,222	COMMUTATOR = '@',223	RESTRICT = _int_contained_sel,224	JOIN = _int_contained_joinsel225);226 227--------------228CREATE FUNCTION intset(int4)229RETURNS _int4230AS 'MODULE_PATHNAME'231LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;232 233CREATE FUNCTION icount(_int4)234RETURNS int4235AS 'MODULE_PATHNAME'236LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;237 238CREATE OPERATOR # (239	RIGHTARG = _int4,240	PROCEDURE = icount241);242 243CREATE FUNCTION sort(_int4, text)244RETURNS _int4245AS 'MODULE_PATHNAME'246LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;247 248CREATE FUNCTION sort(_int4)249RETURNS _int4250AS 'MODULE_PATHNAME'251LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;252 253CREATE FUNCTION sort_asc(_int4)254RETURNS _int4255AS 'MODULE_PATHNAME'256LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;257 258CREATE FUNCTION sort_desc(_int4)259RETURNS _int4260AS 'MODULE_PATHNAME'261LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;262 263CREATE FUNCTION uniq(_int4)264RETURNS _int4265AS 'MODULE_PATHNAME'266LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;267 268CREATE FUNCTION idx(_int4, int4)269RETURNS int4270AS 'MODULE_PATHNAME'271LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;272 273CREATE OPERATOR # (274	LEFTARG = _int4,275	RIGHTARG = int4,276	PROCEDURE = idx277);278 279CREATE FUNCTION subarray(_int4, int4, int4)280RETURNS _int4281AS 'MODULE_PATHNAME'282LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;283 284CREATE FUNCTION subarray(_int4, int4)285RETURNS _int4286AS 'MODULE_PATHNAME'287LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;288 289CREATE FUNCTION intarray_push_elem(_int4, int4)290RETURNS _int4291AS 'MODULE_PATHNAME'292LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;293 294CREATE OPERATOR + (295	LEFTARG = _int4,296	RIGHTARG = int4,297	PROCEDURE = intarray_push_elem298);299 300CREATE FUNCTION intarray_push_array(_int4, _int4)301RETURNS _int4302AS 'MODULE_PATHNAME'303LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;304 305CREATE OPERATOR + (306	LEFTARG = _int4,307	RIGHTARG = _int4,308	COMMUTATOR = +,309	PROCEDURE = intarray_push_array310);311 312CREATE FUNCTION intarray_del_elem(_int4, int4)313RETURNS _int4314AS 'MODULE_PATHNAME'315LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;316 317CREATE OPERATOR - (318	LEFTARG = _int4,319	RIGHTARG = int4,320	PROCEDURE = intarray_del_elem321);322 323CREATE FUNCTION intset_union_elem(_int4, int4)324RETURNS _int4325AS 'MODULE_PATHNAME'326LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;327 328CREATE OPERATOR | (329	LEFTARG = _int4,330	RIGHTARG = int4,331	PROCEDURE = intset_union_elem332);333 334CREATE OPERATOR | (335	LEFTARG = _int4,336	RIGHTARG = _int4,337	COMMUTATOR = |,338	PROCEDURE = _int_union339);340 341CREATE FUNCTION intset_subtract(_int4, _int4)342RETURNS _int4343AS 'MODULE_PATHNAME'344LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;345 346CREATE OPERATOR - (347	LEFTARG = _int4,348	RIGHTARG = _int4,349	PROCEDURE = intset_subtract350);351 352CREATE OPERATOR & (353	LEFTARG = _int4,354	RIGHTARG = _int4,355	COMMUTATOR = &,356	PROCEDURE = _int_inter357);358--------------359 360-- define the GiST support methods361CREATE FUNCTION g_int_consistent(internal,_int4,smallint,oid,internal)362RETURNS bool363AS 'MODULE_PATHNAME'364LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;365 366CREATE FUNCTION g_int_compress(internal)367RETURNS internal368AS 'MODULE_PATHNAME'369LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;370 371CREATE FUNCTION g_int_decompress(internal)372RETURNS internal373AS 'MODULE_PATHNAME'374LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;375 376CREATE FUNCTION g_int_penalty(internal,internal,internal)377RETURNS internal378AS 'MODULE_PATHNAME'379LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;380 381CREATE FUNCTION g_int_picksplit(internal, internal)382RETURNS internal383AS 'MODULE_PATHNAME'384LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;385 386CREATE FUNCTION g_int_union(internal, internal)387RETURNS _int4388AS 'MODULE_PATHNAME'389LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;390 391CREATE FUNCTION g_int_same(_int4, _int4, internal)392RETURNS internal393AS 'MODULE_PATHNAME'394LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;395 396 397-- Create the operator class for indexing398 399CREATE OPERATOR CLASS gist__int_ops400DEFAULT FOR TYPE _int4 USING gist AS401	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-- intbig419---------------------------------------------420-- define the GiST support methods421 422CREATE FUNCTION _intbig_in(cstring)423RETURNS intbig_gkey424AS 'MODULE_PATHNAME'425LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;426 427CREATE FUNCTION _intbig_out(intbig_gkey)428RETURNS cstring429AS 'MODULE_PATHNAME'430LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;431 432CREATE TYPE intbig_gkey (433        INTERNALLENGTH = -1,434        INPUT = _intbig_in,435        OUTPUT = _intbig_out436);437 438CREATE FUNCTION g_intbig_consistent(internal,_int4,smallint,oid,internal)439RETURNS bool440AS 'MODULE_PATHNAME'441LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;442 443CREATE FUNCTION g_intbig_compress(internal)444RETURNS internal445AS 'MODULE_PATHNAME'446LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;447 448CREATE FUNCTION g_intbig_decompress(internal)449RETURNS internal450AS 'MODULE_PATHNAME'451LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;452 453CREATE FUNCTION g_intbig_penalty(internal,internal,internal)454RETURNS internal455AS 'MODULE_PATHNAME'456LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;457 458CREATE FUNCTION g_intbig_picksplit(internal, internal)459RETURNS internal460AS 'MODULE_PATHNAME'461LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;462 463CREATE FUNCTION g_intbig_union(internal, internal)464RETURNS intbig_gkey465AS 'MODULE_PATHNAME'466LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;467 468CREATE FUNCTION g_intbig_same(intbig_gkey, intbig_gkey, internal)469RETURNS internal470AS 'MODULE_PATHNAME'471LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;472 473-- register the opclass for indexing (not as default)474 475CREATE OPERATOR CLASS gist__intbig_ops476FOR TYPE _int4 USING gist477AS478	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--GIN495 496CREATE FUNCTION ginint4_queryextract(_int4, internal, int2, internal, internal, internal, internal)497RETURNS internal498AS 'MODULE_PATHNAME'499LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;500 501CREATE FUNCTION ginint4_consistent(internal, int2, _int4, int4, internal, internal, internal, internal)502RETURNS bool503AS 'MODULE_PATHNAME'504LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;505 506CREATE OPERATOR CLASS gin__int_ops507FOR TYPE _int4 USING gin508AS509	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