1 /* contrib/ltree/ltree--1.1.sql */
3 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
4 \echo Use "CREATE EXTENSION ltree" to load this file. \quit
6 CREATE FUNCTION ltree_in(cstring)
9 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
11 CREATE FUNCTION ltree_out(ltree)
14 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
24 --Compare function for ltree
25 CREATE FUNCTION ltree_cmp(ltree,ltree)
28 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
30 CREATE FUNCTION ltree_lt(ltree,ltree)
33 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
35 CREATE FUNCTION ltree_le(ltree,ltree)
38 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
40 CREATE FUNCTION ltree_eq(ltree,ltree)
43 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
45 CREATE FUNCTION ltree_ge(ltree,ltree)
48 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
50 CREATE FUNCTION ltree_gt(ltree,ltree)
53 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
55 CREATE FUNCTION ltree_ne(ltree,ltree)
58 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
104 PROCEDURE = ltree_eq,
116 PROCEDURE = ltree_ne,
125 CREATE FUNCTION subltree(ltree,int4,int4)
128 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
130 CREATE FUNCTION subpath(ltree,int4,int4)
133 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
135 CREATE FUNCTION subpath(ltree,int4)
138 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
140 CREATE FUNCTION index(ltree,ltree)
142 AS 'MODULE_PATHNAME', 'ltree_index'
143 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
145 CREATE FUNCTION index(ltree,ltree,int4)
147 AS 'MODULE_PATHNAME', 'ltree_index'
148 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
150 CREATE FUNCTION nlevel(ltree)
153 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
155 CREATE FUNCTION ltree2text(ltree)
158 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
160 CREATE FUNCTION text2ltree(text)
163 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
165 CREATE FUNCTION lca(_ltree)
167 AS 'MODULE_PATHNAME','_lca'
168 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
170 CREATE FUNCTION lca(ltree,ltree)
173 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
175 CREATE FUNCTION lca(ltree,ltree,ltree)
178 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
180 CREATE FUNCTION lca(ltree,ltree,ltree,ltree)
183 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
185 CREATE FUNCTION lca(ltree,ltree,ltree,ltree,ltree)
188 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
190 CREATE FUNCTION lca(ltree,ltree,ltree,ltree,ltree,ltree)
193 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
195 CREATE FUNCTION lca(ltree,ltree,ltree,ltree,ltree,ltree,ltree)
198 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
200 CREATE FUNCTION lca(ltree,ltree,ltree,ltree,ltree,ltree,ltree,ltree)
203 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
205 CREATE FUNCTION ltree_isparent(ltree,ltree)
208 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
210 CREATE FUNCTION ltree_risparent(ltree,ltree)
213 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
215 CREATE FUNCTION ltree_addltree(ltree,ltree)
218 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
220 CREATE FUNCTION ltree_addtext(ltree,text)
223 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
225 CREATE FUNCTION ltree_textadd(text,ltree)
228 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
230 CREATE FUNCTION ltreeparentsel(internal, oid, internal, integer)
233 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
238 PROCEDURE = ltree_isparent,
240 RESTRICT = ltreeparentsel,
244 CREATE OPERATOR ^@> (
247 PROCEDURE = ltree_isparent,
256 PROCEDURE = ltree_risparent,
258 RESTRICT = ltreeparentsel,
262 CREATE OPERATOR ^<@ (
265 PROCEDURE = ltree_risparent,
274 PROCEDURE = ltree_addltree
280 PROCEDURE = ltree_addtext
286 PROCEDURE = ltree_textadd
292 CREATE OPERATOR CLASS ltree_ops
293 DEFAULT FOR TYPE ltree USING btree AS
299 FUNCTION 1 ltree_cmp(ltree, ltree);
303 CREATE FUNCTION lquery_in(cstring)
306 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
308 CREATE FUNCTION lquery_out(lquery)
311 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
320 CREATE FUNCTION ltq_regex(ltree,lquery)
323 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
325 CREATE FUNCTION ltq_rregex(lquery,ltree)
328 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
333 PROCEDURE = ltq_regex,
342 PROCEDURE = ltq_rregex,
352 PROCEDURE = ltq_regex,
361 PROCEDURE = ltq_rregex,
367 CREATE FUNCTION lt_q_regex(ltree,_lquery)
370 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
372 CREATE FUNCTION lt_q_rregex(_lquery,ltree)
375 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
380 PROCEDURE = lt_q_regex,
389 PROCEDURE = lt_q_rregex,
399 PROCEDURE = lt_q_regex,
408 PROCEDURE = lt_q_rregex,
414 CREATE FUNCTION ltxtq_in(cstring)
417 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
419 CREATE FUNCTION ltxtq_out(ltxtquery)
422 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
424 CREATE TYPE ltxtquery (
431 -- operations WITH ltxtquery
433 CREATE FUNCTION ltxtq_exec(ltree, ltxtquery)
436 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
438 CREATE FUNCTION ltxtq_rexec(ltxtquery, ltree)
441 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
445 RIGHTARG = ltxtquery,
446 PROCEDURE = ltxtq_exec,
455 PROCEDURE = ltxtq_rexec,
464 RIGHTARG = ltxtquery,
465 PROCEDURE = ltxtq_exec,
474 PROCEDURE = ltxtq_rexec,
480 --GiST support for ltree
481 CREATE FUNCTION ltree_gist_in(cstring)
484 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
486 CREATE FUNCTION ltree_gist_out(ltree_gist)
489 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
491 CREATE TYPE ltree_gist (
493 input = ltree_gist_in,
494 output = ltree_gist_out,
499 CREATE FUNCTION ltree_consistent(internal,ltree,int2,oid,internal)
500 RETURNS bool as 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
502 CREATE FUNCTION ltree_compress(internal)
503 RETURNS internal as 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
505 CREATE FUNCTION ltree_decompress(internal)
506 RETURNS internal as 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
508 CREATE FUNCTION ltree_penalty(internal,internal,internal)
509 RETURNS internal as 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
511 CREATE FUNCTION ltree_picksplit(internal, internal)
512 RETURNS internal as 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
514 CREATE FUNCTION ltree_union(internal, internal)
515 RETURNS ltree_gist as 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
517 CREATE FUNCTION ltree_same(ltree_gist, ltree_gist, internal)
518 RETURNS internal as 'MODULE_PATHNAME' LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
520 CREATE OPERATOR CLASS gist_ltree_ops
521 DEFAULT FOR TYPE ltree USING gist AS
529 OPERATOR 12 ~ (ltree, lquery) ,
530 OPERATOR 13 ~ (lquery, ltree) ,
531 OPERATOR 14 @ (ltree, ltxtquery) ,
532 OPERATOR 15 @ (ltxtquery, ltree) ,
533 OPERATOR 16 ? (ltree, _lquery) ,
534 OPERATOR 17 ? (_lquery, ltree) ,
535 FUNCTION 1 ltree_consistent (internal, ltree, int2, oid, internal),
536 FUNCTION 2 ltree_union (internal, internal),
537 FUNCTION 3 ltree_compress (internal),
538 FUNCTION 4 ltree_decompress (internal),
539 FUNCTION 5 ltree_penalty (internal, internal, internal),
540 FUNCTION 6 ltree_picksplit (internal, internal),
541 FUNCTION 7 ltree_same (ltree_gist, ltree_gist, internal),
547 CREATE FUNCTION _ltree_isparent(_ltree,ltree)
550 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
552 CREATE FUNCTION _ltree_r_isparent(ltree,_ltree)
555 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
557 CREATE FUNCTION _ltree_risparent(_ltree,ltree)
560 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
562 CREATE FUNCTION _ltree_r_risparent(ltree,_ltree)
565 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
567 CREATE FUNCTION _ltq_regex(_ltree,lquery)
570 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
572 CREATE FUNCTION _ltq_rregex(lquery,_ltree)
575 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
577 CREATE FUNCTION _lt_q_regex(_ltree,_lquery)
580 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
582 CREATE FUNCTION _lt_q_rregex(_lquery,_ltree)
585 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
587 CREATE FUNCTION _ltxtq_exec(_ltree, ltxtquery)
590 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
592 CREATE FUNCTION _ltxtq_rexec(ltxtquery, _ltree)
595 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
600 PROCEDURE = _ltree_isparent,
609 PROCEDURE = _ltree_r_isparent,
618 PROCEDURE = _ltree_risparent,
627 PROCEDURE = _ltree_r_risparent,
636 PROCEDURE = _ltq_regex,
645 PROCEDURE = _ltq_rregex,
654 PROCEDURE = _lt_q_regex,
663 PROCEDURE = _lt_q_rregex,
671 RIGHTARG = ltxtquery,
672 PROCEDURE = _ltxtq_exec,
681 PROCEDURE = _ltxtq_rexec,
689 CREATE OPERATOR ^@> (
692 PROCEDURE = _ltree_isparent,
698 CREATE OPERATOR ^<@ (
701 PROCEDURE = _ltree_r_isparent,
707 CREATE OPERATOR ^<@ (
710 PROCEDURE = _ltree_risparent,
716 CREATE OPERATOR ^@> (
719 PROCEDURE = _ltree_r_risparent,
728 PROCEDURE = _ltq_regex,
737 PROCEDURE = _ltq_rregex,
746 PROCEDURE = _lt_q_regex,
755 PROCEDURE = _lt_q_rregex,
763 RIGHTARG = ltxtquery,
764 PROCEDURE = _ltxtq_exec,
773 PROCEDURE = _ltxtq_rexec,
780 CREATE FUNCTION _ltree_extract_isparent(_ltree,ltree)
783 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
785 CREATE OPERATOR ?@> (
788 PROCEDURE = _ltree_extract_isparent
791 CREATE FUNCTION _ltree_extract_risparent(_ltree,ltree)
794 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
796 CREATE OPERATOR ?<@ (
799 PROCEDURE = _ltree_extract_risparent
802 CREATE FUNCTION _ltq_extract_regex(_ltree,lquery)
805 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
810 PROCEDURE = _ltq_extract_regex
813 CREATE FUNCTION _ltxtq_extract_exec(_ltree,ltxtquery)
816 LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
820 RIGHTARG = ltxtquery,
821 PROCEDURE = _ltxtq_extract_exec
824 --GiST support for ltree[]
825 CREATE FUNCTION _ltree_consistent(internal,_ltree,int2,oid,internal)
828 LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
830 CREATE FUNCTION _ltree_compress(internal)
833 LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
835 CREATE FUNCTION _ltree_penalty(internal,internal,internal)
838 LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
840 CREATE FUNCTION _ltree_picksplit(internal, internal)
843 LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
845 CREATE FUNCTION _ltree_union(internal, internal)
848 LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
850 CREATE FUNCTION _ltree_same(ltree_gist, ltree_gist, internal)
853 LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
855 CREATE OPERATOR CLASS gist__ltree_ops
856 DEFAULT FOR TYPE _ltree USING gist AS
857 OPERATOR 10 <@ (_ltree, ltree),
858 OPERATOR 11 @> (ltree, _ltree),
859 OPERATOR 12 ~ (_ltree, lquery),
860 OPERATOR 13 ~ (lquery, _ltree),
861 OPERATOR 14 @ (_ltree, ltxtquery),
862 OPERATOR 15 @ (ltxtquery, _ltree),
863 OPERATOR 16 ? (_ltree, _lquery),
864 OPERATOR 17 ? (_lquery, _ltree),
865 FUNCTION 1 _ltree_consistent (internal, _ltree, int2, oid, internal),
866 FUNCTION 2 _ltree_union (internal, internal),
867 FUNCTION 3 _ltree_compress (internal),
868 FUNCTION 4 ltree_decompress (internal),
869 FUNCTION 5 _ltree_penalty (internal, internal, internal),
870 FUNCTION 6 _ltree_picksplit (internal, internal),
871 FUNCTION 7 _ltree_same (ltree_gist, ltree_gist, internal),