tsearch2.sql

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

-- Adjust this setting to control where the objects get CREATEd.
SET search_path = public;

BEGIN;

--dict conf
CREATE TABLE pg_ts_dict (
	dict_name	text not null primary key,
	dict_init	regprocedure,
	dict_initoption	text,
	dict_lexize	regprocedure not null,
	dict_comment	text
) with oids;

--dict interface
CREATE FUNCTION lexize(oid, text) 
	RETURNS _text
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

CREATE FUNCTION lexize(text, text)
        RETURNS _text
        as '$libdir/tsearch2', 'lexize_byname'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT;

CREATE FUNCTION lexize(text)
        RETURNS _text
        as '$libdir/tsearch2', 'lexize_bycurrent'
        LANGUAGE C
        RETURNS NULL ON NULL INPUT;

CREATE FUNCTION set_curdict(int)
	RETURNS void
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

CREATE FUNCTION set_curdict(text)
	RETURNS void
	as '$libdir/tsearch2', 'set_curdict_byname'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

--built-in dictionaries
CREATE FUNCTION dex_init(internal)
	RETURNS internal
	as '$libdir/tsearch2' 
	LANGUAGE C;

CREATE FUNCTION dex_lexize(internal,internal,int4)
	RETURNS internal
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

insert into pg_ts_dict select 
	'simple', 
	'dex_init(internal)',
	null,
	'dex_lexize(internal,internal,int4)',
	'Simple example of dictionary.'
;
	 
CREATE FUNCTION snb_en_init(internal)
	RETURNS internal
	as '$libdir/tsearch2' 
	LANGUAGE C;

CREATE FUNCTION snb_lexize(internal,internal,int4)
	RETURNS internal
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

insert into pg_ts_dict select 
	'en_stem', 
	'snb_en_init(internal)',
	'contrib/english.stop',
	'snb_lexize(internal,internal,int4)',
	'English Stemmer. Snowball.'
;

CREATE FUNCTION snb_ru_init_koi8(internal)
	RETURNS internal
	as '$libdir/tsearch2' 
	LANGUAGE C;

insert into pg_ts_dict select 
	'ru_stem_koi8', 
	'snb_ru_init_koi8(internal)',
	'contrib/russian.stop',
	'snb_lexize(internal,internal,int4)',
	'Russian Stemmer. Snowball. KOI8 Encoding'
;
	 
CREATE FUNCTION snb_ru_init_utf8(internal)
	RETURNS internal
	as '$libdir/tsearch2' 
	LANGUAGE C;

insert into pg_ts_dict select 
	'ru_stem_utf8', 
	'snb_ru_init_utf8(internal)',
	'contrib/russian.stop.utf8',
	'snb_lexize(internal,internal,int4)',
	'Russian Stemmer. Snowball. UTF8 Encoding'
;
	 
CREATE FUNCTION spell_init(internal)
	RETURNS internal
	as '$libdir/tsearch2' 
	LANGUAGE C;

CREATE FUNCTION spell_lexize(internal,internal,int4)
	RETURNS internal
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

insert into pg_ts_dict select 
	'ispell_template', 
	'spell_init(internal)',
	null,
	'spell_lexize(internal,internal,int4)',
	'ISpell interface. Must have .dict and .aff files'
;

CREATE FUNCTION syn_init(internal)
	RETURNS internal
	as '$libdir/tsearch2' 
	LANGUAGE C;

CREATE FUNCTION syn_lexize(internal,internal,int4)
	RETURNS internal
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

insert into pg_ts_dict select 
	'synonym', 
	'syn_init(internal)',
	null,
	'syn_lexize(internal,internal,int4)',
	'Example of synonym dictionary'
;

CREATE FUNCTION thesaurus_init(internal)
	RETURNS internal
	as '$libdir/tsearch2' 
	LANGUAGE C;

CREATE FUNCTION thesaurus_lexize(internal,internal,int4,internal)
	RETURNS internal
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

insert into pg_ts_dict select 
	'thesaurus_template', 
	'thesaurus_init(internal)',
	null,
	'thesaurus_lexize(internal,internal,int4,internal)',
	'Thesaurus template, must be pointed Dictionary and DictFile'
;

--dict conf
CREATE TABLE pg_ts_parser (
	prs_name	text not null primary key,
	prs_start	regprocedure not null,
	prs_nexttoken	regprocedure not null,
	prs_end		regprocedure not null,
	prs_headline	regprocedure not null,
	prs_lextype	regprocedure not null,
	prs_comment	text
) with oids;

--sql-level interface
CREATE TYPE tokentype 
	as (tokid int4, alias text, descr text); 

CREATE FUNCTION token_type(int4)
	RETURNS setof tokentype
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

CREATE FUNCTION token_type(text)
	RETURNS setof tokentype
	as '$libdir/tsearch2', 'token_type_byname'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

CREATE FUNCTION token_type()
	RETURNS setof tokentype
	as '$libdir/tsearch2', 'token_type_current'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

CREATE FUNCTION set_curprs(int)
	RETURNS void
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

CREATE FUNCTION set_curprs(text)
	RETURNS void
	as '$libdir/tsearch2', 'set_curprs_byname'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

CREATE TYPE tokenout 
	as (tokid int4, token text);

CREATE FUNCTION parse(oid,text)
	RETURNS setof tokenout
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;
 
CREATE FUNCTION parse(text,text)
	RETURNS setof tokenout
	as '$libdir/tsearch2', 'parse_byname'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;
 
CREATE FUNCTION parse(text)
	RETURNS setof tokenout
	as '$libdir/tsearch2', 'parse_current'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;
 
--default parser
CREATE FUNCTION prsd_start(internal,int4)
	RETURNS internal
	as '$libdir/tsearch2'
	LANGUAGE C;

CREATE FUNCTION prsd_getlexeme(internal,internal,internal)
	RETURNS int4
	as '$libdir/tsearch2'
	LANGUAGE C;

CREATE FUNCTION prsd_end(internal)
	RETURNS void
	as '$libdir/tsearch2'
	LANGUAGE C;

CREATE FUNCTION prsd_lextype(internal)
	RETURNS internal
	as '$libdir/tsearch2'
	LANGUAGE C;

CREATE FUNCTION prsd_headline(internal,internal,internal)
	RETURNS internal
	as '$libdir/tsearch2'
	LANGUAGE C;

insert into pg_ts_parser select
	'default',
	'prsd_start(internal,int4)',
	'prsd_getlexeme(internal,internal,internal)',
	'prsd_end(internal)',
	'prsd_headline(internal,internal,internal)',
	'prsd_lextype(internal)',
	'Parser from OpenFTS v0.34'
;	

--tsearch config

CREATE TABLE pg_ts_cfg (
	ts_name		text not null primary key,
	prs_name	text not null,
	locale		text
) with oids;

CREATE TABLE pg_ts_cfgmap (
	ts_name		text not null,
	tok_alias	text not null,
	dict_name	text[],
	primary key (ts_name,tok_alias)
) with oids;

CREATE FUNCTION set_curcfg(int)
	RETURNS void
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

CREATE FUNCTION set_curcfg(text)
	RETURNS void
	as '$libdir/tsearch2', 'set_curcfg_byname'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

CREATE FUNCTION show_curcfg()
	RETURNS oid
	as '$libdir/tsearch2'
	LANGUAGE C
	RETURNS NULL ON NULL INPUT;

insert into pg_ts_cfg values ('default', 'default','C');
insert into pg_ts_cfg values ('default_russian', 'default','ru_RU.KOI8-R');
insert into pg_ts_cfg values ('utf8_russian', 'default','ru_RU.UTF-8');
insert into pg_ts_cfg values ('simple', 'default');

insert into pg_ts_cfgmap values ('default', 'lword', '{en_stem}');
insert into pg_ts_cfgmap values ('default', 'nlword', '{simple}');
insert into pg_ts_cfgmap values ('default', 'word', '{simple}');
insert into pg_ts_cfgmap values ('default', 'email', '{simple}');
insert into pg_ts_cfgmap values ('default', 'url', '{simple}');
insert into pg_ts_cfgmap values ('default', 'host', '{simple}');
insert into pg_ts_cfgmap values ('default', 'sfloat', '{simple}');
insert into pg_ts_cfgmap values ('default', 'version', '{simple}');
insert into pg_ts_cfgmap values ('default', 'part_hword', '{simple}');
insert into pg_ts_cfgmap values ('default', 'nlpart_hword', '{simple}');
insert into pg_ts_cfgmap values ('default', 'lpart_hword', '{en_stem}');
insert into pg_ts_cfgmap values ('default', 'hword', '{simple}');
insert into pg_ts_cfgmap values ('default', 'lhword', '{en_stem}');
insert into pg_ts_cfgmap values ('default', 'nlhword', '{simple}');
insert into pg_ts_cfgmap values ('default', 'uri', '{simple}');
insert into pg_ts_cfgmap values ('default', 'file', '{simple}');
insert into pg_ts_cfgmap values ('default', 'float', '{simple}');
insert into pg_ts_cfgmap values ('default', 'int', '{simple}');
insert into pg_ts_cfgmap values ('default', 'uint', '{simple}');
insert into pg_ts_cfgmap values ('default_russian', 'lword', '{en_stem}');
insert into pg_ts_cfgmap values ('default_russian', 'nlword', '{ru_stem_koi8}');
insert into pg_ts_cfgmap values ('default_russian', 'word', '{ru_stem_koi8}');
insert into pg_ts_cfgmap values ('default_russian', 'email', '{simple}');
insert into pg_ts_cfgmap values ('default_russian', 'url', '{simple}');
insert into pg_ts_cfgmap values ('default_russian', 'host', '{simple}');
insert into pg_ts_cfgmap values ('default_russian', 'sfloat', '{simple}');
insert into pg_ts_cfgmap values ('default_russian', 'version', '{simple}');
insert into pg_ts_cfgmap values ('default_russian', 'part_hword', '{simple}');
insert into pg_ts_cfgmap values ('default_russian', 'nlpart_hword', '{ru_stem_koi8}');
insert into pg_ts_cfgmap values ('default_russian', 'lpart_hword', '{en_stem}');
insert into pg_ts_cfgmap values ('default_russian', 'hword', '{ru_stem_koi8}');
insert into pg_ts_cfgmap values ('default_russian', 'lhword', '{en_stem}');
insert into pg_ts_cfgmap values ('default_russian', 'nlhword', '{ru_stem_koi8}');
insert into pg_ts_cfgmap values ('default_russian', 'uri', '{simple}');
insert into pg_ts_cfgmap values ('default_russian', 'file', '{simple}');
insert into pg_ts_cfgmap values ('default_russian', 'float', '{simple}');
insert into pg_ts_cfgmap values ('default_russian', 'int', '{simple}');
insert into pg_ts_cfgmap values ('default_russian', 'uint', '{simple}');
insert into pg_ts_cfgmap values ('utf8_russian', 'lword', '{en_stem}');
insert into pg_ts_cfgmap values ('utf8_russian', 'nlword', '{ru_stem_utf8}');
insert into pg_ts_cfgmap values ('utf8_russian', 'word', '{ru_stem_utf8}');
insert into pg_ts_cfgmap values ('utf8_russian', 'email', '{simple}');
insert into pg_ts_cfgmap values ('utf8_russian', 'url', '{simple}');
insert into pg_ts_cfgmap values ('utf8_russian', 'host', '{simple}');
insert into pg_ts_cfgmap values ('utf8_russian', 'sfloat', '{simple}');
insert into pg_ts_cfgmap values ('utf8_russian', 'version', '{simple}');
insert into pg_ts_cfgmap values ('utf8_russian', 'part_hword', '{simple}');
insert into pg_ts_cfgmap values ('utf8_russian', 'nlpart_hword', '{ru_stem_utf8}');
insert into pg_ts_cfgmap values ('utf8_russian', 'lpart_hword', '{en_stem}');
insert into pg_ts_cfgmap values ('utf8_russian', 'hword', '{ru_stem_utf8}');
insert into pg_ts_cfgmap values ('utf8_russian', 'lhword', '{en_stem}');
insert into pg_ts_cfgmap values ('utf8_russian', 'nlhword', '{ru_stem_utf8}');
insert into pg_ts_cfgmap values ('utf8_russian', 'uri', '{simple}');
insert into pg_ts_cfgmap values ('utf8_russian', 'file', '{simple}');
insert into pg_ts_cfgmap values ('utf8_russian', 'float', '{simple}');
insert into pg_ts_cfgmap values ('utf8_russian', 'int', '{simple}');
insert into pg_ts_cfgmap values ('utf8_russian', 'uint', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'lword', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'nlword', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'word', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'email', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'url', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'host', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'sfloat', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'version', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'part_hword', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'nlpart_hword', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'lpart_hword', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'hword', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'lhword', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'nlhword', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'uri', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'file', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'float', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'int', '{simple}');
insert into pg_ts_cfgmap values ('simple', 'uint', '{simple}');

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

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

CREATE TYPE tsvector (
        INTERNALLENGTH = -1,
        INPUT = tsvector_in,
        OUTPUT = tsvector_out,
        STORAGE = extended
);

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

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

CREATE FUNCTION to_tsvector(text, text)
RETURNS tsvector
AS '$libdir/tsearch2', 'to_tsvector_name'