1/* contrib/pg_trgm/pg_trgm--1.3.sql */ 2 3-- complain if script is sourced in psql, rather than via CREATE EXTENSION 4\echo Use "CREATE EXTENSION pg_trgm" to load this file. \quit 5 6-- Deprecated function 7CREATE FUNCTION set_limit(float4) 8RETURNS float4 9AS 'MODULE_PATHNAME' 10LANGUAGE C STRICT VOLATILE PARALLEL UNSAFE; 11 12-- Deprecated function 13CREATE FUNCTION show_limit() 14RETURNS float4 15AS 'MODULE_PATHNAME' 16LANGUAGE C STRICT STABLE PARALLEL SAFE; 17 18CREATE FUNCTION show_trgm(text) 19RETURNS _text 20AS 'MODULE_PATHNAME' 21LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 22 23CREATE FUNCTION similarity(text,text) 24RETURNS float4 25AS 'MODULE_PATHNAME' 26LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 27 28CREATE FUNCTION similarity_op(text,text) 29RETURNS bool 30AS 'MODULE_PATHNAME' 31LANGUAGE C STRICT STABLE PARALLEL SAFE; -- stable because depends on pg_trgm.similarity_threshold 32 33CREATE OPERATOR % ( 34 LEFTARG = text, 35 RIGHTARG = text, 36 PROCEDURE = similarity_op, 37 COMMUTATOR = '%', 38 RESTRICT = contsel, 39 JOIN = contjoinsel 40); 41 42CREATE FUNCTION word_similarity(text,text) 43RETURNS float4 44AS 'MODULE_PATHNAME' 45LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 46 47CREATE FUNCTION word_similarity_op(text,text) 48RETURNS bool 49AS 'MODULE_PATHNAME' 50LANGUAGE C STRICT STABLE PARALLEL SAFE; -- stable because depends on pg_trgm.word_similarity_threshold 51 52CREATE FUNCTION word_similarity_commutator_op(text,text) 53RETURNS bool 54AS 'MODULE_PATHNAME' 55LANGUAGE C STRICT STABLE PARALLEL SAFE; -- stable because depends on pg_trgm.word_similarity_threshold 56 57CREATE OPERATOR <% ( 58 LEFTARG = text, 59 RIGHTARG = text, 60 PROCEDURE = word_similarity_op, 61 COMMUTATOR = '%>', 62 RESTRICT = contsel, 63 JOIN = contjoinsel 64); 65 66CREATE OPERATOR %> ( 67 LEFTARG = text, 68 RIGHTARG = text, 69 PROCEDURE = word_similarity_commutator_op, 70 COMMUTATOR = '<%', 71 RESTRICT = contsel, 72 JOIN = contjoinsel 73); 74 75CREATE FUNCTION similarity_dist(text,text) 76RETURNS float4 77AS 'MODULE_PATHNAME' 78LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 79 80CREATE OPERATOR <-> ( 81 LEFTARG = text, 82 RIGHTARG = text, 83 PROCEDURE = similarity_dist, 84 COMMUTATOR = '<->' 85); 86 87CREATE FUNCTION word_similarity_dist_op(text,text) 88RETURNS float4 89AS 'MODULE_PATHNAME' 90LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 91 92CREATE FUNCTION word_similarity_dist_commutator_op(text,text) 93RETURNS float4 94AS 'MODULE_PATHNAME' 95LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 96 97CREATE OPERATOR <<-> ( 98 LEFTARG = text, 99 RIGHTARG = text, 100 PROCEDURE = word_similarity_dist_op, 101 COMMUTATOR = '<->>' 102); 103 104CREATE OPERATOR <->> ( 105 LEFTARG = text, 106 RIGHTARG = text, 107 PROCEDURE = word_similarity_dist_commutator_op, 108 COMMUTATOR = '<<->' 109); 110 111-- gist key 112CREATE FUNCTION gtrgm_in(cstring) 113RETURNS gtrgm 114AS 'MODULE_PATHNAME' 115LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 116 117CREATE FUNCTION gtrgm_out(gtrgm) 118RETURNS cstring 119AS 'MODULE_PATHNAME' 120LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 121 122CREATE TYPE gtrgm ( 123 INTERNALLENGTH = -1, 124 INPUT = gtrgm_in, 125 OUTPUT = gtrgm_out 126); 127 128-- support functions for gist 129CREATE FUNCTION gtrgm_consistent(internal,text,smallint,oid,internal) 130RETURNS bool 131AS 'MODULE_PATHNAME' 132LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 133 134CREATE FUNCTION gtrgm_distance(internal,text,smallint,oid,internal) 135RETURNS float8 136AS 'MODULE_PATHNAME' 137LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 138 139CREATE FUNCTION gtrgm_compress(internal) 140RETURNS internal 141AS 'MODULE_PATHNAME' 142LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 143 144CREATE FUNCTION gtrgm_decompress(internal) 145RETURNS internal 146AS 'MODULE_PATHNAME' 147LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 148 149CREATE FUNCTION gtrgm_penalty(internal,internal,internal) 150RETURNS internal 151AS 'MODULE_PATHNAME' 152LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 153 154CREATE FUNCTION gtrgm_picksplit(internal, internal) 155RETURNS internal 156AS 'MODULE_PATHNAME' 157LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 158 159CREATE FUNCTION gtrgm_union(internal, internal) 160RETURNS gtrgm 161AS 'MODULE_PATHNAME' 162LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 163 164CREATE FUNCTION gtrgm_same(gtrgm, gtrgm, internal) 165RETURNS internal 166AS 'MODULE_PATHNAME' 167LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 168 169-- create the operator class for gist 170CREATE OPERATOR CLASS gist_trgm_ops 171FOR TYPE text USING gist 172AS 173 OPERATOR 1 % (text, text), 174 FUNCTION 1 gtrgm_consistent (internal, text, smallint, oid, internal), 175 FUNCTION 2 gtrgm_union (internal, internal), 176 FUNCTION 3 gtrgm_compress (internal), 177 FUNCTION 4 gtrgm_decompress (internal), 178 FUNCTION 5 gtrgm_penalty (internal, internal, internal), 179 FUNCTION 6 gtrgm_picksplit (internal, internal), 180 FUNCTION 7 gtrgm_same (gtrgm, gtrgm, internal), 181 STORAGE gtrgm; 182 183-- Add operators and support functions that are new in 9.1. We do it like 184-- this, leaving them "loose" in the operator family rather than bound into 185-- the gist_trgm_ops opclass, because that's the only state that can be 186-- reproduced during an upgrade from 9.0 (see pg_trgm--unpackaged--1.0.sql). 187 188ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD 189 OPERATOR 2 <-> (text, text) FOR ORDER BY pg_catalog.float_ops, 190 OPERATOR 3 pg_catalog.~~ (text, text), 191 OPERATOR 4 pg_catalog.~~* (text, text), 192 FUNCTION 8 (text, text) gtrgm_distance (internal, text, smallint, oid, internal); 193 194-- Add operators that are new in 9.3. 195 196ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD 197 OPERATOR 5 pg_catalog.~ (text, text), 198 OPERATOR 6 pg_catalog.~* (text, text); 199 200-- Add operators that are new in 9.6 (pg_trgm 1.2). 201 202ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD 203 OPERATOR 7 %> (text, text), 204 OPERATOR 8 <->> (text, text) FOR ORDER BY pg_catalog.float_ops; 205 206-- support functions for gin 207CREATE FUNCTION gin_extract_value_trgm(text, internal) 208RETURNS internal 209AS 'MODULE_PATHNAME' 210LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 211 212CREATE FUNCTION gin_extract_query_trgm(text, internal, int2, internal, internal, internal, internal) 213RETURNS internal 214AS 'MODULE_PATHNAME' 215LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 216 217CREATE FUNCTION gin_trgm_consistent(internal, int2, text, int4, internal, internal, internal, internal) 218RETURNS bool 219AS 'MODULE_PATHNAME' 220LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 221 222-- create the operator class for gin 223CREATE OPERATOR CLASS gin_trgm_ops 224FOR TYPE text USING gin 225AS 226 OPERATOR 1 % (text, text), 227 FUNCTION 1 btint4cmp (int4, int4), 228 FUNCTION 2 gin_extract_value_trgm (text, internal), 229 FUNCTION 3 gin_extract_query_trgm (text, internal, int2, internal, internal, internal, internal), 230 FUNCTION 4 gin_trgm_consistent (internal, int2, text, int4, internal, internal, internal, internal), 231 STORAGE int4; 232 233-- Add operators that are new in 9.1. 234 235ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD 236 OPERATOR 3 pg_catalog.~~ (text, text), 237 OPERATOR 4 pg_catalog.~~* (text, text); 238 239-- Add operators that are new in 9.3. 240 241ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD 242 OPERATOR 5 pg_catalog.~ (text, text), 243 OPERATOR 6 pg_catalog.~* (text, text); 244 245-- Add functions that are new in 9.6 (pg_trgm 1.2). 246 247CREATE FUNCTION gin_trgm_triconsistent(internal, int2, text, int4, internal, internal, internal) 248RETURNS "char" 249AS 'MODULE_PATHNAME' 250LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 251 252ALTER OPERATOR FAMILY gin_trgm_ops USING gin ADD 253 OPERATOR 7 %> (text, text), 254 FUNCTION 6 (text,text) gin_trgm_triconsistent (internal, int2, text, int4, internal, internal, internal); 255