codekingpro/portable-devtools

1/* contrib/pg_trgm/pg_trgm--1.3.sql */2 3-- complain if script is sourced in psql, rather than via CREATE EXTENSION4\echo Use "CREATE EXTENSION pg_trgm" to load this file. \quit5 6-- Deprecated function7CREATE FUNCTION set_limit(float4)8RETURNS float49AS 'MODULE_PATHNAME'10LANGUAGE C STRICT VOLATILE PARALLEL UNSAFE;11 12-- Deprecated function13CREATE FUNCTION show_limit()14RETURNS float415AS 'MODULE_PATHNAME'16LANGUAGE C STRICT STABLE PARALLEL SAFE;17 18CREATE FUNCTION show_trgm(text)19RETURNS _text20AS 'MODULE_PATHNAME'21LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;22 23CREATE FUNCTION similarity(text,text)24RETURNS float425AS 'MODULE_PATHNAME'26LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;27 28CREATE FUNCTION similarity_op(text,text)29RETURNS bool30AS 'MODULE_PATHNAME'31LANGUAGE C STRICT STABLE PARALLEL SAFE;  -- stable because depends on pg_trgm.similarity_threshold32 33CREATE OPERATOR % (34        LEFTARG = text,35        RIGHTARG = text,36        PROCEDURE = similarity_op,37        COMMUTATOR = '%',38        RESTRICT = contsel,39        JOIN = contjoinsel40);41 42CREATE FUNCTION word_similarity(text,text)43RETURNS float444AS 'MODULE_PATHNAME'45LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;46 47CREATE FUNCTION word_similarity_op(text,text)48RETURNS bool49AS 'MODULE_PATHNAME'50LANGUAGE C STRICT STABLE PARALLEL SAFE;  -- stable because depends on pg_trgm.word_similarity_threshold51 52CREATE FUNCTION word_similarity_commutator_op(text,text)53RETURNS bool54AS 'MODULE_PATHNAME'55LANGUAGE C STRICT STABLE PARALLEL SAFE;  -- stable because depends on pg_trgm.word_similarity_threshold56 57CREATE OPERATOR <% (58        LEFTARG = text,59        RIGHTARG = text,60        PROCEDURE = word_similarity_op,61        COMMUTATOR = '%>',62        RESTRICT = contsel,63        JOIN = contjoinsel64);65 66CREATE OPERATOR %> (67        LEFTARG = text,68        RIGHTARG = text,69        PROCEDURE = word_similarity_commutator_op,70        COMMUTATOR = '<%',71        RESTRICT = contsel,72        JOIN = contjoinsel73);74 75CREATE FUNCTION similarity_dist(text,text)76RETURNS float477AS '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 float489AS 'MODULE_PATHNAME'90LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;91 92CREATE FUNCTION word_similarity_dist_commutator_op(text,text)93RETURNS float494AS '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 key112CREATE FUNCTION gtrgm_in(cstring)113RETURNS gtrgm114AS 'MODULE_PATHNAME'115LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;116 117CREATE FUNCTION gtrgm_out(gtrgm)118RETURNS cstring119AS 'MODULE_PATHNAME'120LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;121 122CREATE TYPE gtrgm (123        INTERNALLENGTH = -1,124        INPUT = gtrgm_in,125        OUTPUT = gtrgm_out126);127 128-- support functions for gist129CREATE FUNCTION gtrgm_consistent(internal,text,smallint,oid,internal)130RETURNS bool131AS 'MODULE_PATHNAME'132LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;133 134CREATE FUNCTION gtrgm_distance(internal,text,smallint,oid,internal)135RETURNS float8136AS 'MODULE_PATHNAME'137LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;138 139CREATE FUNCTION gtrgm_compress(internal)140RETURNS internal141AS 'MODULE_PATHNAME'142LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;143 144CREATE FUNCTION gtrgm_decompress(internal)145RETURNS internal146AS 'MODULE_PATHNAME'147LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;148 149CREATE FUNCTION gtrgm_penalty(internal,internal,internal)150RETURNS internal151AS 'MODULE_PATHNAME'152LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;153 154CREATE FUNCTION gtrgm_picksplit(internal, internal)155RETURNS internal156AS 'MODULE_PATHNAME'157LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;158 159CREATE FUNCTION gtrgm_union(internal, internal)160RETURNS gtrgm161AS 'MODULE_PATHNAME'162LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;163 164CREATE FUNCTION gtrgm_same(gtrgm, gtrgm, internal)165RETURNS internal166AS 'MODULE_PATHNAME'167LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;168 169-- create the operator class for gist170CREATE OPERATOR CLASS gist_trgm_ops171FOR TYPE text USING gist172AS173        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 like184-- this, leaving them "loose" in the operator family rather than bound into185-- the gist_trgm_ops opclass, because that's the only state that could be186-- reproduced during an upgrade from 9.0.187 188ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD189        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 ADD197        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 ADD203        OPERATOR        7       %> (text, text),204        OPERATOR        8       <->> (text, text) FOR ORDER BY pg_catalog.float_ops;205 206-- support functions for gin207CREATE FUNCTION gin_extract_value_trgm(text, internal)208RETURNS internal209AS 'MODULE_PATHNAME'210LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;211 212CREATE FUNCTION gin_extract_query_trgm(text, internal, int2, internal, internal, internal, internal)213RETURNS internal214AS 'MODULE_PATHNAME'215LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;216 217CREATE FUNCTION gin_trgm_consistent(internal, int2, text, int4, internal, internal, internal, internal)218RETURNS bool219AS 'MODULE_PATHNAME'220LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;221 222-- create the operator class for gin223CREATE OPERATOR CLASS gin_trgm_ops224FOR TYPE text USING gin225AS226        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 ADD236        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 ADD242        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 ADD253        OPERATOR        7       %> (text, text),254        FUNCTION        6      (text,text) gin_trgm_triconsistent (internal, int2, text, int4, internal, internal, internal);255