tsearch2.sql

PostgreSQL 8.2中增加了很多企业用户所需要的功能和性能上的提高,其开发团队说,该版本将加速更多企业向该数据库移植.核心开发成员之一Bruce Momjian表示,在新版PostgreSQL

);

CREATE OPERATOR >= (
        LEFTARG = tsvector,
        RIGHTARG = tsvector,
        PROCEDURE = tsvector_ge,
        COMMUTATOR = '<=',
        NEGATOR = '<',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

CREATE OPERATOR > (
        LEFTARG = tsvector,
        RIGHTARG = tsvector,
        PROCEDURE = tsvector_gt,
        COMMUTATOR = '<',
        NEGATOR = '<=',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

CREATE OPERATOR = (
        LEFTARG = tsvector,
        RIGHTARG = tsvector,
        PROCEDURE = tsvector_eq,
        COMMUTATOR = '=',
        NEGATOR = '<>',
        RESTRICT = eqsel,
        JOIN = eqjoinsel,
        SORT1 = '<',
        SORT2 = '<'
);

CREATE OPERATOR <> (
        LEFTARG = tsvector,
        RIGHTARG = tsvector,
        PROCEDURE = tsvector_ne,
        COMMUTATOR = '<>',
        NEGATOR = '=',
        RESTRICT = neqsel,
        JOIN = neqjoinsel
);

CREATE OPERATOR CLASS tsvector_ops
    DEFAULT FOR TYPE tsvector USING btree AS
        OPERATOR        1       < ,
        OPERATOR        2       <= , 
        OPERATOR        3       = ,
        OPERATOR        4       >= ,
        OPERATOR        5       > ,
        FUNCTION        1       tsvector_cmp(tsvector, tsvector);

----------------Compare functions and operators for tsquery
CREATE OR REPLACE FUNCTION tsquery_cmp(tsquery,tsquery)
RETURNS int4
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OR REPLACE FUNCTION tsquery_lt(tsquery,tsquery)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OR REPLACE FUNCTION tsquery_le(tsquery,tsquery)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OR REPLACE FUNCTION tsquery_eq(tsquery,tsquery)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OR REPLACE FUNCTION tsquery_ge(tsquery,tsquery)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OR REPLACE FUNCTION tsquery_gt(tsquery,tsquery)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OR REPLACE FUNCTION tsquery_ne(tsquery,tsquery)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;


CREATE OPERATOR < (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsquery_lt,
        COMMUTATOR = '>',
        NEGATOR = '>=',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

CREATE OPERATOR <= (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsquery_le,
        COMMUTATOR = '>=',
        NEGATOR = '>',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

CREATE OPERATOR >= (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsquery_ge,
        COMMUTATOR = '<=',
        NEGATOR = '<',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

CREATE OPERATOR > (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsquery_gt,
        COMMUTATOR = '<',
        NEGATOR = '<=',
        RESTRICT = contsel,
        JOIN = contjoinsel
);


CREATE OPERATOR = (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsquery_eq,
        COMMUTATOR = '=',
        NEGATOR = '<>',
        RESTRICT = eqsel,
        JOIN = eqjoinsel,
        SORT1 = '<',
        SORT2 = '<'
);

CREATE OPERATOR <> (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsquery_ne,
        COMMUTATOR = '<>',
        NEGATOR = '=',
        RESTRICT = neqsel,
        JOIN = neqjoinsel
);

CREATE OPERATOR CLASS tsquery_ops
    DEFAULT FOR TYPE tsquery USING btree AS
        OPERATOR        1       < ,
        OPERATOR        2       <= ,
        OPERATOR        3       = ,
        OPERATOR        4       >= ,
        OPERATOR        5       > ,
        FUNCTION        1       tsquery_cmp(tsquery, tsquery);

CREATE OR REPLACE FUNCTION numnode(tsquery)
        RETURNS int4
        as '$libdir/tsearch2', 'tsquery_numnode'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OR REPLACE FUNCTION tsquery_and(tsquery,tsquery)
        RETURNS tsquery
        as '$libdir/tsearch2', 'tsquery_and'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OPERATOR && (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsquery_and,
        COMMUTATOR = '&&',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

CREATE OR REPLACE FUNCTION tsquery_or(tsquery,tsquery)
        RETURNS tsquery
        as '$libdir/tsearch2', 'tsquery_or'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OPERATOR || (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsquery_or,
        COMMUTATOR = '||',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

CREATE OR REPLACE FUNCTION tsquery_not(tsquery)
        RETURNS tsquery
        as '$libdir/tsearch2', 'tsquery_not'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OPERATOR !! (
        RIGHTARG = tsquery,
        PROCEDURE = tsquery_not
);

--------------rewrite subsystem

CREATE OR REPLACE FUNCTION rewrite(tsquery, text)
        RETURNS tsquery
        as '$libdir/tsearch2', 'tsquery_rewrite'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OR REPLACE FUNCTION rewrite(tsquery, tsquery, tsquery)
        RETURNS tsquery
        as '$libdir/tsearch2', 'tsquery_rewrite_query'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OR REPLACE FUNCTION rewrite_accum(tsquery,tsquery[])
        RETURNS tsquery
        AS '$libdir/tsearch2'
        LANGUAGE C;

CREATE OR REPLACE FUNCTION rewrite_finish(tsquery)
      RETURNS tsquery
      as '$libdir/tsearch2'
      LANGUAGE C;

CREATE AGGREGATE rewrite (
      BASETYPE=tsquery[],
      SFUNC=rewrite_accum,
      STYPE=tsquery,
      FINALFUNC = rewrite_finish
);

CREATE OR REPLACE FUNCTION tsq_mcontains(tsquery, tsquery)
        RETURNS bool
        as '$libdir/tsearch2'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OR REPLACE FUNCTION tsq_mcontained(tsquery, tsquery)
        RETURNS bool
        as '$libdir/tsearch2'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OPERATOR @> (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsq_mcontains,
        COMMUTATOR = '<@',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

CREATE OPERATOR <@ (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsq_mcontained,
        COMMUTATOR = '@>',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

-- obsolete:
CREATE OPERATOR @ (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsq_mcontains,
        COMMUTATOR = '~',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

CREATE OPERATOR ~ (
        LEFTARG = tsquery,
        RIGHTARG = tsquery,
        PROCEDURE = tsq_mcontained,
        COMMUTATOR = '@',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

-----------gist support of rewrite------------------

CREATE FUNCTION gtsq_in(cstring)
RETURNS gtsq
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT;

CREATE FUNCTION gtsq_out(gtsq)
RETURNS cstring
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT;

CREATE TYPE gtsq (
        INTERNALLENGTH = 8,
        INPUT = gtsq_in,
        OUTPUT = gtsq_out
);

CREATE FUNCTION gtsq_consistent(gtsq,internal,int4)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C;

CREATE FUNCTION gtsq_compress(internal)
RETURNS internal
AS '$libdir/tsearch2'
LANGUAGE C;

CREATE FUNCTION gtsq_decompress(internal)
RETURNS internal
AS '$libdir/tsearch2'
LANGUAGE C;

CREATE FUNCTION gtsq_penalty(internal,internal,internal)
RETURNS internal
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT;

CREATE FUNCTION gtsq_picksplit(internal, internal)
RETURNS internal
AS '$libdir/tsearch2'
LANGUAGE C;

CREATE FUNCTION gtsq_union(bytea, internal)
RETURNS _int4
AS '$libdir/tsearch2'
LANGUAGE C;

CREATE FUNCTION gtsq_same(gtsq, gtsq, internal)
RETURNS internal
AS '$libdir/tsearch2'
LANGUAGE C;

CREATE OPERATOR CLASS gist_tp_tsquery_ops
DEFAULT FOR TYPE tsquery USING gist
AS
        OPERATOR        7       @> (tsquery, tsquery) RECHECK,
        OPERATOR        8       <@ (tsquery, tsquery) RECHECK,
        OPERATOR        13      @ (tsquery, tsquery) RECHECK,
        OPERATOR        14      ~ (tsquery, tsquery) RECHECK,
        FUNCTION        1       gtsq_consistent (gtsq, internal, int4),
        FUNCTION        2       gtsq_union (bytea, internal),
        FUNCTION        3       gtsq_compress (internal),
        FUNCTION        4       gtsq_decompress (internal),
        FUNCTION        5       gtsq_penalty (internal, internal, internal),
        FUNCTION        6       gtsq_picksplit (internal, internal),
        FUNCTION        7       gtsq_same (gtsq, gtsq, internal),
        STORAGE         gtsq;

--GIN support function
CREATE FUNCTION gin_extract_tsvector(tsvector,internal)
RETURNS internal
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT;

CREATE FUNCTION gin_extract_tsquery(tsquery,internal,internal)
RETURNS internal
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT;

CREATE FUNCTION gin_ts_consistent(internal,internal,tsquery)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT;

CREATE OPERATOR @@@ (
        LEFTARG = tsvector,
        RIGHTARG = tsquery,
        PROCEDURE = exectsq,
        COMMUTATOR = '@@@',
        RESTRICT = contsel,
        JOIN = contjoinsel
);
CREATE OPERATOR @@@ (
        LEFTARG = tsquery,
        RIGHTARG = tsvector,
        PROCEDURE = rexectsq,
        COMMUTATOR = '@@@',
        RESTRICT = contsel,
        JOIN = contjoinsel
);

CREATE OPERATOR CLASS gin_tsvector_ops
DEFAULT FOR TYPE tsvector USING gin
AS
        OPERATOR        1       @@ (tsvector, tsquery),
        OPERATOR        2       @@@ (tsvector, tsquery) RECHECK,
        FUNCTION        1       bttextcmp(text, text),
        FUNCTION        2       gin_extract_tsvector(tsvector,internal),
        FUNCTION        3       gin_extract_tsquery(tsquery,internal,internal),
        FUNCTION        4       gin_ts_consistent(internal,internal,tsquery),
        STORAGE         text;


--example of ISpell dictionary
--update pg_ts_dict set dict_initoption='DictFile="/usr/local/share/ispell/russian.dict" ,AffFile ="/usr/local/share/ispell/russian.aff", StopFile="/usr/local/share/ispell/russian.stop"' where dict_name='ispell_template';

--example of synonym dict
--update pg_ts_dict set dict_initoption='/usr/local/share/ispell/english.syn' where dict_name='synonym';

--example of thesaurus dict
--update pg_ts_dict set dict_initoption='DictFile="contrib/thesaurus", Dictionary="en_stem"' where dict_name='thesaurus_template';
--update pg_ts_cfgmap set dict_name = '{thesaurus_template,en_stem}' where dict_name = '{en_stem}';
END;