diff options
Diffstat (limited to 'contrib/ltree/ltree.sql.in')
| -rw-r--r-- | contrib/ltree/ltree.sql.in | 849 |
1 files changed, 849 insertions, 0 deletions
diff --git a/contrib/ltree/ltree.sql.in b/contrib/ltree/ltree.sql.in new file mode 100644 index 0000000000..b40a01a02c --- /dev/null +++ b/contrib/ltree/ltree.sql.in @@ -0,0 +1,849 @@ +BEGIN; + +CREATE FUNCTION ltree_in(opaque) +RETURNS opaque +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict); + +CREATE FUNCTION ltree_out(opaque) +RETURNS opaque +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict); + +CREATE TYPE ltree ( +internallength = -1, +input = ltree_in, +output = ltree_out, +storage = extended +); + + +--Compare function for ltree +CREATE FUNCTION ltree_cmp(ltree,ltree) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltree_lt(ltree,ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltree_le(ltree,ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltree_eq(ltree,ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltree_ge(ltree,ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltree_gt(ltree,ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltree_ne(ltree,ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + + +CREATE OPERATOR < ( + LEFTARG = ltree, RIGHTARG = ltree, PROCEDURE = ltree_lt, + COMMUTATOR = '>', NEGATOR = '>=', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR <= ( + LEFTARG = ltree, RIGHTARG = ltree, PROCEDURE = ltree_le, + COMMUTATOR = '>=', NEGATOR = '>', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR >= ( + LEFTARG = ltree, RIGHTARG = ltree, PROCEDURE = ltree_ge, + COMMUTATOR = '<=', NEGATOR = '<', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR > ( + LEFTARG = ltree, RIGHTARG = ltree, PROCEDURE = ltree_gt, + COMMUTATOR = '<', NEGATOR = '<=', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR = ( + LEFTARG = ltree, RIGHTARG = ltree, PROCEDURE = ltree_eq, + COMMUTATOR = '=', NEGATOR = '<>', + RESTRICT = eqsel, JOIN = eqjoinsel, + SORT1 = '<', SORT2 = '<' +); + +CREATE OPERATOR <> ( + LEFTARG = ltree, RIGHTARG = ltree, PROCEDURE = ltree_ne, + COMMUTATOR = '<>', NEGATOR = '=', + RESTRICT = neqsel, JOIN = neqjoinsel +); + +--util functions + +CREATE FUNCTION subltree(ltree,int4,int4) +RETURNS ltree +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION subpath(ltree,int4,int4) +RETURNS ltree +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION subpath(ltree,int4) +RETURNS ltree +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION nlevel(ltree) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltree_isparent(ltree,ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltree_risparent(ltree,ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltree_addltree(ltree,ltree) +RETURNS ltree +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltree_addtext(ltree,text) +RETURNS ltree +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltree_textadd(text,ltree) +RETURNS ltree +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE OPERATOR @> ( + LEFTARG = ltree, RIGHTARG = ltree, PROCEDURE = ltree_isparent, + COMMUTATOR = '<@', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ^@> ( + LEFTARG = ltree, RIGHTARG = ltree, PROCEDURE = ltree_isparent, + COMMUTATOR = '^<@', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR <@ ( + LEFTARG = ltree, RIGHTARG = ltree, PROCEDURE = ltree_risparent, + COMMUTATOR = '@>', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ^<@ ( + LEFTARG = ltree, RIGHTARG = ltree, PROCEDURE = ltree_risparent, + COMMUTATOR = '^@>', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR || ( + LEFTARG = ltree, RIGHTARG = ltree, PROCEDURE = ltree_addltree +); + +CREATE OPERATOR || ( + LEFTARG = ltree, RIGHTARG = text, PROCEDURE = ltree_addtext +); + +CREATE OPERATOR || ( + LEFTARG = text, RIGHTARG = ltree, PROCEDURE = ltree_textadd +); + + +-- B-tree support +INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype) + VALUES ( + (SELECT oid FROM pg_am WHERE amname = 'btree'), + 'ltree_ops', + (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), + 1, -- UID of superuser is hardwired to 1 as of PG 7.3 + (SELECT oid FROM pg_type WHERE typname = 'ltree'), + true, + 0); + +SELECT o.oid AS opoid, o.oprname + INTO TEMP TABLE ltree_ops_tmp + FROM pg_operator o, pg_type t + WHERE o.oprleft = t.oid and o.oprright = t.oid + and t.typname = 'ltree'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 1, false, c.opoid + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') AND + opcname = 'ltree_ops' AND + c.oprname = '<'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 2, false, c.opoid + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') AND + opcname = 'ltree_ops' AND + c.oprname = '<='; +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 3, false, c.opoid + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') AND + opcname = 'ltree_ops' AND + c.oprname = '='; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 4, false, c.opoid + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') AND + opcname = 'ltree_ops' AND + c.oprname = '>='; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 5, false, c.opoid + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') AND + opcname = 'ltree_ops' AND + c.oprname = '>'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 1, p.oid + FROM pg_opclass opcl, pg_proc p + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') AND + opcname = 'ltree_ops' AND + p.proname = 'ltree_cmp'; + +drop table ltree_ops_tmp; + +--lquery type +CREATE FUNCTION lquery_in(opaque) +RETURNS opaque +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict); + +CREATE FUNCTION lquery_out(opaque) +RETURNS opaque +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict); + +CREATE TYPE lquery ( +internallength = -1, +input = lquery_in, +output = lquery_out, +storage = extended +); + +CREATE FUNCTION ltq_regex(ltree,lquery) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION ltq_rregex(lquery,ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE OPERATOR ~ ( + LEFTARG = ltree, RIGHTARG = lquery, PROCEDURE = ltq_regex, + COMMUTATOR = '~', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ~ ( + LEFTARG = lquery, RIGHTARG = ltree, PROCEDURE = ltq_rregex, + COMMUTATOR = '~', + RESTRICT = contsel, JOIN = contjoinsel +); + +--not-indexed +CREATE OPERATOR ^~ ( + LEFTARG = ltree, RIGHTARG = lquery, PROCEDURE = ltq_regex, + COMMUTATOR = '^~', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ^~ ( + LEFTARG = lquery, RIGHTARG = ltree, PROCEDURE = ltq_rregex, + COMMUTATOR = '^~', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE FUNCTION ltxtq_in(opaque) +RETURNS opaque +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict); + +CREATE FUNCTION ltxtq_out(opaque) +RETURNS opaque +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict); + +CREATE TYPE ltxtquery ( +internallength = -1, +input = ltxtq_in, +output = ltxtq_out, +storage = extended +); + +-- operations with ltxtquery + +CREATE FUNCTION ltxtq_exec(ltree, ltxtquery) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict, iscachable); + +CREATE FUNCTION ltxtq_rexec(ltxtquery, ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict, iscachable); + +CREATE OPERATOR @ ( + LEFTARG = ltree, RIGHTARG = ltxtquery, PROCEDURE = ltxtq_exec, + COMMUTATOR = '@', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR @ ( + LEFTARG = ltxtquery, RIGHTARG = ltree, PROCEDURE = ltxtq_rexec, + COMMUTATOR = '@', + RESTRICT = contsel, JOIN = contjoinsel +); + +--not-indexed +CREATE OPERATOR ^@ ( + LEFTARG = ltree, RIGHTARG = ltxtquery, PROCEDURE = ltxtq_exec, + COMMUTATOR = '^@', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ^@ ( + LEFTARG = ltxtquery, RIGHTARG = ltree, PROCEDURE = ltxtq_rexec, + COMMUTATOR = '^@', + RESTRICT = contsel, JOIN = contjoinsel +); + +--GiST support for ltree +CREATE FUNCTION ltree_gist_in(opaque) +RETURNS opaque +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict); + +CREATE FUNCTION ltree_gist_out(opaque) +RETURNS opaque +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict); + +CREATE TYPE ltree_gist ( + internallength = -1, + input = ltree_gist_in, + output = ltree_gist_out, + storage = plain +); + + +create function ltree_consistent(opaque,opaque,int2) returns bool as 'MODULE_PATHNAME' language 'C'; +create function ltree_compress(opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; +create function ltree_decompress(opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; +create function ltree_penalty(opaque,opaque,opaque) returns opaque as 'MODULE_PATHNAME' language 'C' with(isstrict); +create function ltree_picksplit(opaque, opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; +create function ltree_union(bytea, opaque) returns int4 as 'MODULE_PATHNAME' language 'C'; +create function ltree_same(opaque, opaque, opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; + +INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opckeytype, opcdefault) + SELECT pg_am.oid, 'gist_ltree_ops', + (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), + 1, -- UID of superuser is hardwired to 1 as of PG 7.3 + pg_type.oid, pg_key.oid, true + FROM pg_type, pg_am, pg_type pg_key + WHERE pg_type.typname = 'ltree' and + pg_am.amname='gist' and + pg_key.typname = 'ltree_gist'; + +SELECT o.oid AS opoid, o.oprname +INTO TABLE ltree_ops_tmp +FROM pg_operator o, pg_type t +WHERE o.oprleft = t.oid and o.oprright = t.oid + and t.typname = 'ltree'; + +INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) + SELECT opcl.oid, c.opoid, 1, 'f' + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE opcname = 'gist_ltree_ops' + and c.oprname = '<'; + +INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) + SELECT opcl.oid, c.opoid, 2, 'f' + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE opcname = 'gist_ltree_ops' + and c.oprname = '<='; + +INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) + SELECT opcl.oid, c.opoid, 3, 'f' + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE opcname = 'gist_ltree_ops' + and c.oprname = '='; + +INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) + SELECT opcl.oid, c.opoid, 4, 'f' + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE opcname = 'gist_ltree_ops' + and c.oprname = '>='; + +INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck) + SELECT opcl.oid, c.opoid, 5, 'f' + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE opcname = 'gist_ltree_ops' + and c.oprname = '>'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 10, false, c.opoid + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and c.oprname = '@>'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 11, false, c.opoid + FROM pg_opclass opcl, ltree_ops_tmp c + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and c.oprname = '<@'; + +DROP TABLE ltree_ops_tmp; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 12, false, o.oid + FROM pg_opclass opcl, pg_operator o, pg_type t, pg_type tq + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and t.typname = 'ltree' and tq.typname = 'lquery' + and o.oprleft = t.oid and o.oprright = tq.oid + and o.oprname = '~'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 13, false, o.oid + FROM pg_opclass opcl, pg_operator o, pg_type t, pg_type tq + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and t.typname = 'lquery' and tq.typname = 'ltree' + and o.oprleft = t.oid and o.oprright = tq.oid + and o.oprname = '~'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 14, false, o.oid + FROM pg_opclass opcl, pg_operator o, pg_type t, pg_type tq + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and t.typname = 'ltree' and tq.typname = 'ltxtquery' + and o.oprleft = t.oid and o.oprright = tq.oid + and o.oprname = '@'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 15, false, o.oid + FROM pg_opclass opcl, pg_operator o, pg_type t, pg_type tq + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and t.typname = 'ltxtquery' and tq.typname = 'ltree' + and o.oprleft = t.oid and o.oprright = tq.oid + and o.oprname = '@'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 1, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and proname = 'ltree_consistent'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 2, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and proname = 'ltree_union'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 3, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and proname = 'ltree_compress'; +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 4, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and proname = 'ltree_decompress'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 5, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and proname = 'ltree_penalty'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 6, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and proname = 'ltree_picksplit'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 7, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist_ltree_ops' + and proname = 'ltree_same'; + +-- arrays of ltree + +CREATE FUNCTION _ltree_isparent(_ltree,ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION _ltree_r_isparent(ltree,_ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION _ltree_risparent(_ltree,ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION _ltree_r_risparent(ltree,_ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION _ltq_regex(_ltree,lquery) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION _ltq_rregex(lquery,_ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE FUNCTION _ltxtq_exec(_ltree, ltxtquery) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict, iscachable); + +CREATE FUNCTION _ltxtq_rexec(ltxtquery, _ltree) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict, iscachable); + +CREATE OPERATOR @> ( + LEFTARG = _ltree, RIGHTARG = ltree, PROCEDURE = _ltree_isparent, + COMMUTATOR = '<@', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR <@ ( + LEFTARG = ltree, RIGHTARG = _ltree, PROCEDURE = _ltree_r_isparent, + COMMUTATOR = '@>', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR <@ ( + LEFTARG = _ltree, RIGHTARG = ltree, PROCEDURE = _ltree_risparent, + COMMUTATOR = '@>', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR @> ( + LEFTARG = ltree, RIGHTARG = _ltree, PROCEDURE = _ltree_r_risparent, + COMMUTATOR = '<@', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ~ ( + LEFTARG = _ltree, RIGHTARG = lquery, PROCEDURE = _ltq_regex, + COMMUTATOR = '~', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ~ ( + LEFTARG = lquery, RIGHTARG = _ltree, PROCEDURE = _ltq_rregex, + COMMUTATOR = '~', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR @ ( + LEFTARG = _ltree, RIGHTARG = ltxtquery, PROCEDURE = _ltxtq_exec, + COMMUTATOR = '@', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR @ ( + LEFTARG = ltxtquery, RIGHTARG = _ltree, PROCEDURE = _ltxtq_rexec, + COMMUTATOR = '@', + RESTRICT = contsel, JOIN = contjoinsel +); + + +--not indexed +CREATE OPERATOR ^@> ( + LEFTARG = _ltree, RIGHTARG = ltree, PROCEDURE = _ltree_isparent, + COMMUTATOR = '^<@', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ^<@ ( + LEFTARG = ltree, RIGHTARG = _ltree, PROCEDURE = _ltree_r_isparent, + COMMUTATOR = '^@>', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ^<@ ( + LEFTARG = _ltree, RIGHTARG = ltree, PROCEDURE = _ltree_risparent, + COMMUTATOR = '^@>', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ^@> ( + LEFTARG = ltree, RIGHTARG = _ltree, PROCEDURE = _ltree_r_risparent, + COMMUTATOR = '^<@', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ^~ ( + LEFTARG = _ltree, RIGHTARG = lquery, PROCEDURE = _ltq_regex, + COMMUTATOR = '^~', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ^~ ( + LEFTARG = lquery, RIGHTARG = _ltree, PROCEDURE = _ltq_rregex, + COMMUTATOR = '^~', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ^@ ( + LEFTARG = _ltree, RIGHTARG = ltxtquery, PROCEDURE = _ltxtq_exec, + COMMUTATOR = '^@', + RESTRICT = contsel, JOIN = contjoinsel +); + +CREATE OPERATOR ^@ ( + LEFTARG = ltxtquery, RIGHTARG = _ltree, PROCEDURE = _ltxtq_rexec, + COMMUTATOR = '^@', + RESTRICT = contsel, JOIN = contjoinsel +); + +--extractors +CREATE FUNCTION _ltree_extract_isparent(_ltree,ltree) +RETURNS ltree +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE OPERATOR ?@> ( + LEFTARG = _ltree, RIGHTARG = ltree, PROCEDURE = _ltree_extract_isparent +); + +CREATE FUNCTION _ltree_extract_risparent(_ltree,ltree) +RETURNS ltree +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE OPERATOR ?<@ ( + LEFTARG = _ltree, RIGHTARG = ltree, PROCEDURE = _ltree_extract_risparent +); + +CREATE FUNCTION _ltq_extract_regex(_ltree,lquery) +RETURNS ltree +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE OPERATOR ?~ ( + LEFTARG = _ltree, RIGHTARG = lquery, PROCEDURE = _ltq_extract_regex +); + +CREATE FUNCTION _ltxtq_extract_exec(_ltree,ltxtquery) +RETURNS ltree +AS 'MODULE_PATHNAME' +LANGUAGE 'c' with (isstrict,iscachable); + +CREATE OPERATOR ?@ ( + LEFTARG = _ltree, RIGHTARG = ltxtquery, PROCEDURE = _ltxtq_extract_exec +); + +--GiST support for ltree[] +create function _ltree_consistent(opaque,opaque,int2) returns bool as 'MODULE_PATHNAME' language 'C'; +create function _ltree_compress(opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; +create function _ltree_penalty(opaque,opaque,opaque) returns opaque as 'MODULE_PATHNAME' language 'C' with(isstrict); +create function _ltree_picksplit(opaque, opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; +create function _ltree_union(bytea, opaque) returns int4 as 'MODULE_PATHNAME' language 'C'; +create function _ltree_same(opaque, opaque, opaque) returns opaque as 'MODULE_PATHNAME' language 'C'; + +INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opckeytype, opcdefault) + SELECT pg_am.oid, 'gist__ltree_ops', + (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), + 1, -- UID of superuser is hardwired to 1 as of PG 7.3 + pg_type.oid, pg_key.oid, true + FROM pg_type, pg_am, pg_type pg_key + WHERE pg_type.typname = '_ltree' and + pg_am.amname='gist' and + pg_key.typname = 'ltree_gist'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 12, true, o.oid + FROM pg_opclass opcl, pg_operator o, pg_type t, pg_type tq + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and t.typname = '_ltree' and tq.typname = 'lquery' + and o.oprleft = t.oid and o.oprright = tq.oid + and o.oprname = '~'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 13, true, o.oid + FROM pg_opclass opcl, pg_operator o, pg_type t, pg_type tq + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and t.typname = 'lquery' and tq.typname = '_ltree' + and o.oprleft = t.oid and o.oprright = tq.oid + and o.oprname = '~'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 14, true, o.oid + FROM pg_opclass opcl, pg_operator o, pg_type t, pg_type tq + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and t.typname = '_ltree' and tq.typname = 'ltxtquery' + and o.oprleft = t.oid and o.oprright = tq.oid + and o.oprname = '@'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 15, true, o.oid + FROM pg_opclass opcl, pg_operator o, pg_type t, pg_type tq + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and t.typname = 'ltxtquery' and tq.typname = '_ltree' + and o.oprleft = t.oid and o.oprright = tq.oid + and o.oprname = '@'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 10, true, o.oid + FROM pg_opclass opcl, pg_operator o, pg_type t, pg_type tq + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and t.typname = '_ltree' and tq.typname = 'ltree' + and o.oprleft = t.oid and o.oprright = tq.oid + and o.oprname = '<@'; + +INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) + SELECT opcl.oid, 11, true, o.oid + FROM pg_opclass opcl, pg_operator o, pg_type t, pg_type tq + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and t.typname = 'ltree' and tq.typname = '_ltree' + and o.oprleft = t.oid and o.oprright = tq.oid + and o.oprname = '@>'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 1, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and proname = '_ltree_consistent'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 2, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and proname = '_ltree_union'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 3, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and proname = '_ltree_compress'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 4, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and proname = 'ltree_decompress'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 5, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and proname = '_ltree_penalty'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 6, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and proname = '_ltree_picksplit'; + +INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) + SELECT opcl.oid, 7, pro.oid + FROM pg_opclass opcl, pg_proc pro + WHERE + opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist') + and opcname = 'gist__ltree_ops' + and proname = '_ltree_same'; + +END; |