diff options
Diffstat (limited to 'contrib/pg_trgm/pg_trgm--1.3.sql')
| -rw-r--r-- | contrib/pg_trgm/pg_trgm--1.3.sql | 254 |
1 files changed, 254 insertions, 0 deletions
diff --git a/contrib/pg_trgm/pg_trgm--1.3.sql b/contrib/pg_trgm/pg_trgm--1.3.sql new file mode 100644 index 0000000..4c6edf8 --- /dev/null +++ b/contrib/pg_trgm/pg_trgm--1.3.sql @@ -0,0 +1,254 @@ +/* contrib/pg_trgm/pg_trgm--1.3.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION pg_trgm" to load this file. \quit + +-- Deprecated function +CREATE FUNCTION set_limit(float4) +RETURNS float4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT VOLATILE PARALLEL UNSAFE; + +-- Deprecated function +CREATE FUNCTION show_limit() +RETURNS float4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT STABLE PARALLEL SAFE; + +CREATE FUNCTION show_trgm(text) +RETURNS _text +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; + +CREATE FUNCTION similarity(text,text) +RETURNS float4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; + +CREATE FUNCTION similarity_op(text,text) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT STABLE PARALLEL SAFE; -- stable because depends on pg_trgm.similarity_threshold + +CREATE OPERATOR % ( + LEFTARG = text, + RIGHTARG = text, + PROCEDURE = similarity_op, + COMMUTATOR = '%', + RESTRICT = contsel, + JOIN = contjoinsel +); + +CREATE FUNCTION word_similarity(text,text) +RETURNS float4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; + +CREATE FUNCTION word_similarity_op(text,text) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT STABLE PARALLEL SAFE; -- stable because depends on pg_trgm.word_similarity_threshold + +CREATE FUNCTION word_similarity_commutator_op(text,text) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT STABLE PARALLEL SAFE; -- stable because depends on pg_trgm.word_similarity_threshold + +CREATE OPERATOR <% ( + LEFTARG = text, + RIGHTARG = text, + PROCEDURE = word_similarity_op, + COMMUTATOR = '%>', + RESTRICT = contsel, + JOIN = contjoinsel +); + +CREATE OPERATOR %> ( + LEFTARG = text, + RIGHTARG = text, + PROCEDURE = word_similarity_commutator_op, + COMMUTATOR = '<%', + RESTRICT = contsel, + JOIN = contjoinsel +); + +CREATE FUNCTION similarity_dist(text,text) +RETURNS float4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; + +CREATE OPERATOR <-> ( + LEFTARG = text, + RIGHTARG = text, + PROCEDURE = similarity_dist, + COMMUTATOR = '<->' +); + +CREATE FUNCTION word_similarity_dist_op(text,text) +RETURNS float4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; + +CREATE FUNCTION word_similarity_dist_commutator_op(text,text) +RETURNS float4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; + +CREATE OPERATOR <<-> ( + LEFTARG = text, + RIGHTARG = text, + PROCEDURE = word_similarity_dist_op, + COMMUTATOR = '<->>' +); + +CREATE OPERATOR <->> ( + LEFTARG = text, + RIGHTARG = text, + PROCEDURE = word_similarity_dist_commutator_op, + COMMUTATOR = '<<->' +); + +-- gist key +CREATE FUNCTION gtrgm_in(cstring) +RETURNS gtrgm +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; + +CREATE FUNCTION gtrgm_out(gtrgm) +RETURNS cstring +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; + +CREATE TYPE gtrgm ( + INTERNALLENGTH = -1, + INPUT = gtrgm_in, + OUTPUT = gtrgm_out +); + +-- support functions for gist +CREATE FUNCTION gtrgm_consistent(internal,text,smallint,oid,internal) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +CREATE FUNCTION gtrgm_distance(internal,text,smallint,oid,internal) +RETURNS float8 +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +CREATE FUNCTION gtrgm_compress(internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +CREATE FUNCTION gtrgm_decompress(internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +CREATE FUNCTION gtrgm_penalty(internal,internal,internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +CREATE FUNCTION gtrgm_picksplit(internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +CREATE FUNCTION gtrgm_union(internal, internal) +RETURNS gtrgm +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +CREATE FUNCTION gtrgm_same(gtrgm, gtrgm, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +-- create the operator class for gist +CREATE OPERATOR CLASS gist_trgm_ops +FOR TYPE text USING gist +AS + OPERATOR 1 % (text, text), + FUNCTION 1 gtrgm_consistent (internal, text, smallint, oid, internal), + FUNCTION 2 gtrgm_union (internal, internal), + FUNCTION 3 gtrgm_compress (internal), + FUNCTION 4 gtrgm_decompress (internal), + FUNCTION 5 gtrgm_penalty (internal, internal, internal), + FUNCTION 6 gtrgm_picksplit (internal, internal), + FUNCTION 7 gtrgm_same (gtrgm, gtrgm, internal), + STORAGE gtrgm; + +-- Add operators and support functions that are new in 9.1. We do it like +-- this, leaving them "loose" in the operator family rather than bound into +-- the gist_trgm_ops opclass, because that's the only state that could be +-- reproduced during an upgrade from 9.0. + +ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD + OPERATOR 2 <-> (text, text) FOR ORDER BY pg_catalog.float_ops, + OPERATOR 3 pg_catalog.~~ (text, text), + OPERATOR 4 pg_catalog.~~* (text, text), + FUNCTION 8 (text, text) gtrgm_distance (internal, text, smallint, oid, internal); + +-- Add operators that are new in 9.3. + +ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD + OPERATOR 5 pg_catalog.~ (text, text), + OPERATOR 6 pg_catalog.~* (text, text); + +-- Add operators that are new in 9.6 (pg_trgm 1.2). + +ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD + OPERATOR 7 %> (text, text), + OPERATOR 8 <->> (text, text) FOR ORDER BY pg_catalog.float_ops; + +-- support functions for gin +CREATE FUNCTION gin_extract_value_trgm(text, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +CREATE FUNCTION gin_extract_query_trgm(text, internal, int2, internal, internal, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +CREATE FUNCTION gin_trgm_consistent(internal, int2, text, int4, internal, internal, internal, internal) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +-- create the operator class for gin +CREATE OPERATOR CLASS gin_trgm_ops +FOR TYPE text USING gin +AS + OPERATOR 1 % (text, text), + FUNCTION 1 btint4cmp (int4, int4), + FUNCTION 2 gin_extract_value_trgm (text, internal), + FUNCTION 3 gin_extract_query_trgm (text, internal, int2, internal, internal, internal, internal), + FUNCTION 4 gin_trgm_consistent (internal, int2, text, int4, internal, internal, internal, internal), + STORAGE int4; + +-- Add operators that are new in 9.1. + +ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD + OPERATOR 3 pg_catalog.~~ (text, text), + OPERATOR 4 pg_catalog.~~* (text, text); + +-- Add operators that are new in 9.3. + +ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD + OPERATOR 5 pg_catalog.~ (text, text), + OPERATOR 6 pg_catalog.~* (text, text); + +-- Add functions that are new in 9.6 (pg_trgm 1.2). + +CREATE FUNCTION gin_trgm_triconsistent(internal, int2, text, int4, internal, internal, internal) +RETURNS "char" +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; + +ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD + OPERATOR 7 %> (text, text), + FUNCTION 6 (text,text) gin_trgm_triconsistent (internal, int2, text, int4, internal, internal, internal); |