tsearch2.sql

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

LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION to_tsvector(text)
RETURNS tsvector
AS '$libdir/tsearch2', 'to_tsvector_current'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION strip(tsvector)
RETURNS tsvector
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION setweight(tsvector,"char")
RETURNS tsvector
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION concat(tsvector,tsvector)
RETURNS tsvector
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE OPERATOR || (
        LEFTARG = tsvector,
        RIGHTARG = tsvector,
        PROCEDURE = concat
);

--query type
CREATE FUNCTION tsquery_in(cstring)
RETURNS tsquery
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT;

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

CREATE TYPE tsquery (
        INTERNALLENGTH = -1,
        ALIGNMENT = int4,
        INPUT = tsquery_in,
        OUTPUT = tsquery_out
);

CREATE FUNCTION querytree(tsquery)
RETURNS text
AS '$libdir/tsearch2', 'tsquerytree'
LANGUAGE C RETURNS NULL ON NULL INPUT;

CREATE FUNCTION to_tsquery(oid, text)
RETURNS tsquery
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION to_tsquery(text, text)
RETURNS tsquery
AS '$libdir/tsearch2','to_tsquery_name'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION to_tsquery(text)
RETURNS tsquery
AS '$libdir/tsearch2','to_tsquery_current'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION plainto_tsquery(oid, text)
RETURNS tsquery
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION plainto_tsquery(text, text)
RETURNS tsquery
AS '$libdir/tsearch2','plainto_tsquery_name'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION plainto_tsquery(text)
RETURNS tsquery
AS '$libdir/tsearch2','plainto_tsquery_current'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

--operations
CREATE FUNCTION exectsq(tsvector, tsquery)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;
  
COMMENT ON FUNCTION exectsq(tsvector, tsquery) IS 'boolean operation with text index';

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

COMMENT ON FUNCTION rexectsq(tsquery, tsvector) IS 'boolean operation with text index';

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
);

--Trigger
CREATE FUNCTION tsearch2()
RETURNS trigger
AS '$libdir/tsearch2'
LANGUAGE C;

--Relevation
CREATE FUNCTION rank(float4[], tsvector, tsquery)
RETURNS float4
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION rank(float4[], tsvector, tsquery, int4)
RETURNS float4
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION rank(tsvector, tsquery)
RETURNS float4
AS '$libdir/tsearch2', 'rank_def'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION rank(tsvector, tsquery, int4)
RETURNS float4
AS '$libdir/tsearch2', 'rank_def'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION rank_cd(float4[], tsvector, tsquery)
RETURNS float4
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION rank_cd(float4[], tsvector, tsquery, int4)
RETURNS float4
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION rank_cd(tsvector, tsquery)
RETURNS float4
AS '$libdir/tsearch2', 'rank_cd_def'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION rank_cd(tsvector, tsquery, int4)
RETURNS float4
AS '$libdir/tsearch2', 'rank_cd_def'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION headline(oid, text, tsquery, text)
RETURNS text
AS '$libdir/tsearch2', 'headline'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION headline(oid, text, tsquery)
RETURNS text
AS '$libdir/tsearch2', 'headline'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION headline(text, text, tsquery, text)
RETURNS text
AS '$libdir/tsearch2', 'headline_byname'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION headline(text, text, tsquery)
RETURNS text
AS '$libdir/tsearch2', 'headline_byname'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION headline(text, tsquery, text)
RETURNS text
AS '$libdir/tsearch2', 'headline_current'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION headline(text, tsquery)
RETURNS text
AS '$libdir/tsearch2', 'headline_current'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

--GiST
--GiST key type 
CREATE FUNCTION gtsvector_in(cstring)
RETURNS gtsvector
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT;

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

CREATE TYPE gtsvector (
        INTERNALLENGTH = -1,
        INPUT = gtsvector_in,
        OUTPUT = gtsvector_out
);

-- support FUNCTIONs
CREATE FUNCTION gtsvector_consistent(gtsvector,internal,int4)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C;
  
CREATE FUNCTION gtsvector_compress(internal)
RETURNS internal
AS '$libdir/tsearch2'
LANGUAGE C;

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

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

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

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

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

-- CREATE the OPERATOR class
CREATE OPERATOR CLASS gist_tsvector_ops
DEFAULT FOR TYPE tsvector USING gist
AS
        OPERATOR        1       @@ (tsvector, tsquery)  RECHECK ,
        FUNCTION        1       gtsvector_consistent (gtsvector, internal, int4),
        FUNCTION        2       gtsvector_union (internal, internal),
        FUNCTION        3       gtsvector_compress (internal),
        FUNCTION        4       gtsvector_decompress (internal),
        FUNCTION        5       gtsvector_penalty (internal, internal, internal),
        FUNCTION        6       gtsvector_picksplit (internal, internal),
        FUNCTION        7       gtsvector_same (gtsvector, gtsvector, internal),
        STORAGE         gtsvector;


--stat info
CREATE TYPE statinfo 
	as (word text, ndoc int4, nentry int4);

--CREATE FUNCTION tsstat_in(cstring)
--RETURNS tsstat
--AS '$libdir/tsearch2'
--LANGUAGE C RETURNS NULL ON NULL INPUT;
--
--CREATE FUNCTION tsstat_out(tsstat)
--RETURNS cstring
--AS '$libdir/tsearch2'
--LANGUAGE C RETURNS NULL ON NULL INPUT;
--
--CREATE TYPE tsstat (
--        INTERNALLENGTH = -1,
--        INPUT = tsstat_in,
--        OUTPUT = tsstat_out,
--        STORAGE = plain
--);
--
--CREATE FUNCTION ts_accum(tsstat,tsvector)
--RETURNS tsstat
--AS '$libdir/tsearch2'
--LANGUAGE C RETURNS NULL ON NULL INPUT;
--
--CREATE FUNCTION ts_accum_finish(tsstat)
--	RETURNS setof statinfo
--	as '$libdir/tsearch2'
--	LANGUAGE C
--	RETURNS NULL ON NULL INPUT;
--
--CREATE AGGREGATE stat (
--	BASETYPE=tsvector,
--	SFUNC=ts_accum,
--	STYPE=tsstat,
--	FINALFUNC = ts_accum_finish,
--	initcond = ''
--); 

CREATE FUNCTION stat(text)
	RETURNS setof statinfo
	as '$libdir/tsearch2', 'ts_stat'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

CREATE FUNCTION stat(text,text)
	RETURNS setof statinfo
	as '$libdir/tsearch2', 'ts_stat'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

--reset - just for debuging
CREATE FUNCTION reset_tsearch()
        RETURNS void
        as '$libdir/tsearch2'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT;

--get cover (debug for rank_cd)
CREATE FUNCTION get_covers(tsvector,tsquery)
        RETURNS text
        as '$libdir/tsearch2'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT;

--debug function
create type tsdebug as (
        ts_name text,
        tok_type text,
        description text,
        token   text,
        dict_name text[],
        "tsvector" tsvector
);

CREATE FUNCTION _get_parser_from_curcfg() 
RETURNS text as 
' select prs_name from pg_ts_cfg where oid = show_curcfg() '
LANGUAGE SQL RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION ts_debug(text)
RETURNS setof tsdebug as '
select 
        m.ts_name,
        t.alias as tok_type,
        t.descr as description,
        p.token,
        m.dict_name,
        strip(to_tsvector(p.token)) as tsvector
from
        parse( _get_parser_from_curcfg(), $1 ) as p,
        token_type() as t,
        pg_ts_cfgmap as m,
        pg_ts_cfg as c
where
        t.tokid=p.tokid and
        t.alias = m.tok_alias and 
        m.ts_name=c.ts_name and 
        c.oid=show_curcfg() 
' LANGUAGE SQL RETURNS NULL ON NULL INPUT;

--compare functions
CREATE FUNCTION tsvector_cmp(tsvector,tsvector)
RETURNS int4
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION tsvector_lt(tsvector,tsvector)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION tsvector_le(tsvector,tsvector)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;
        
CREATE FUNCTION tsvector_eq(tsvector,tsvector)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION tsvector_ge(tsvector,tsvector)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;
        
CREATE FUNCTION tsvector_gt(tsvector,tsvector)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

CREATE FUNCTION tsvector_ne(tsvector,tsvector)
RETURNS bool
AS '$libdir/tsearch2'
LANGUAGE C RETURNS NULL ON NULL INPUT IMMUTABLE;

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

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