codekingpro/portable-devtools

1/* contrib/citext/citext--1.4.sql */2 3-- complain if script is sourced in psql, rather than via CREATE EXTENSION4\echo Use "CREATE EXTENSION citext" to load this file. \quit5 6--7--  PostgreSQL code for CITEXT.8--9-- Most I/O functions, and a few others, piggyback on the "text" type10-- functions via the implicit cast to text.11--12 13--14-- Shell type to keep things a bit quieter.15--16 17CREATE TYPE citext;18 19--20--  Input and output functions.21--22CREATE FUNCTION citextin(cstring)23RETURNS citext24AS 'textin'25LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;26 27CREATE FUNCTION citextout(citext)28RETURNS cstring29AS 'textout'30LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;31 32CREATE FUNCTION citextrecv(internal)33RETURNS citext34AS 'textrecv'35LANGUAGE internal STABLE STRICT PARALLEL SAFE;36 37CREATE FUNCTION citextsend(citext)38RETURNS bytea39AS 'textsend'40LANGUAGE internal STABLE STRICT PARALLEL SAFE;41 42--43--  The type itself.44--45 46CREATE TYPE citext (47    INPUT          = citextin,48    OUTPUT         = citextout,49    RECEIVE        = citextrecv,50    SEND           = citextsend,51    INTERNALLENGTH = VARIABLE,52    STORAGE        = extended,53    -- make it a non-preferred member of string type category54    CATEGORY       = 'S',55    PREFERRED      = false,56    COLLATABLE     = true57);58 59--60-- Type casting functions for those situations where the I/O casts don't61-- automatically kick in.62--63 64CREATE FUNCTION citext(bpchar)65RETURNS citext66AS 'rtrim1'67LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;68 69CREATE FUNCTION citext(boolean)70RETURNS citext71AS 'booltext'72LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;73 74CREATE FUNCTION citext(inet)75RETURNS citext76AS 'network_show'77LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;78 79--80--  Implicit and assignment type casts.81--82 83CREATE CAST (citext AS text)    WITHOUT FUNCTION AS IMPLICIT;84CREATE CAST (citext AS varchar) WITHOUT FUNCTION AS IMPLICIT;85CREATE CAST (citext AS bpchar)  WITHOUT FUNCTION AS ASSIGNMENT;86CREATE CAST (text AS citext)    WITHOUT FUNCTION AS ASSIGNMENT;87CREATE CAST (varchar AS citext) WITHOUT FUNCTION AS ASSIGNMENT;88CREATE CAST (bpchar AS citext)  WITH FUNCTION citext(bpchar)  AS ASSIGNMENT;89CREATE CAST (boolean AS citext) WITH FUNCTION citext(boolean) AS ASSIGNMENT;90CREATE CAST (inet AS citext)    WITH FUNCTION citext(inet)    AS ASSIGNMENT;91 92--93-- Operator Functions.94--95 96CREATE FUNCTION citext_eq( citext, citext )97RETURNS bool98AS 'MODULE_PATHNAME'99LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;100 101CREATE FUNCTION citext_ne( citext, citext )102RETURNS bool103AS 'MODULE_PATHNAME'104LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;105 106CREATE FUNCTION citext_lt( citext, citext )107RETURNS bool108AS 'MODULE_PATHNAME'109LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;110 111CREATE FUNCTION citext_le( citext, citext )112RETURNS bool113AS 'MODULE_PATHNAME'114LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;115 116CREATE FUNCTION citext_gt( citext, citext )117RETURNS bool118AS 'MODULE_PATHNAME'119LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;120 121CREATE FUNCTION citext_ge( citext, citext )122RETURNS bool123AS 'MODULE_PATHNAME'124LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;125 126--127-- Operators.128--129 130CREATE OPERATOR = (131    LEFTARG    = CITEXT,132    RIGHTARG   = CITEXT,133    COMMUTATOR = =,134    NEGATOR    = <>,135    PROCEDURE  = citext_eq,136    RESTRICT   = eqsel,137    JOIN       = eqjoinsel,138    HASHES,139    MERGES140);141 142CREATE OPERATOR <> (143    LEFTARG    = CITEXT,144    RIGHTARG   = CITEXT,145    NEGATOR    = =,146    COMMUTATOR = <>,147    PROCEDURE  = citext_ne,148    RESTRICT   = neqsel,149    JOIN       = neqjoinsel150);151 152CREATE OPERATOR < (153    LEFTARG    = CITEXT,154    RIGHTARG   = CITEXT,155    NEGATOR    = >=,156    COMMUTATOR = >,157    PROCEDURE  = citext_lt,158    RESTRICT   = scalarltsel,159    JOIN       = scalarltjoinsel160);161 162CREATE OPERATOR <= (163    LEFTARG    = CITEXT,164    RIGHTARG   = CITEXT,165    NEGATOR    = >,166    COMMUTATOR = >=,167    PROCEDURE  = citext_le,168    RESTRICT   = scalarltsel,169    JOIN       = scalarltjoinsel170);171 172CREATE OPERATOR >= (173    LEFTARG    = CITEXT,174    RIGHTARG   = CITEXT,175    NEGATOR    = <,176    COMMUTATOR = <=,177    PROCEDURE  = citext_ge,178    RESTRICT   = scalargtsel,179    JOIN       = scalargtjoinsel180);181 182CREATE OPERATOR > (183    LEFTARG    = CITEXT,184    RIGHTARG   = CITEXT,185    NEGATOR    = <=,186    COMMUTATOR = <,187    PROCEDURE  = citext_gt,188    RESTRICT   = scalargtsel,189    JOIN       = scalargtjoinsel190);191 192--193-- Support functions for indexing.194--195 196CREATE FUNCTION citext_cmp(citext, citext)197RETURNS int4198AS 'MODULE_PATHNAME'199LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;200 201CREATE FUNCTION citext_hash(citext)202RETURNS int4203AS 'MODULE_PATHNAME'204LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;205 206--207-- The btree indexing operator class.208--209 210CREATE OPERATOR CLASS citext_ops211DEFAULT FOR TYPE CITEXT USING btree AS212    OPERATOR    1   <  (citext, citext),213    OPERATOR    2   <= (citext, citext),214    OPERATOR    3   =  (citext, citext),215    OPERATOR    4   >= (citext, citext),216    OPERATOR    5   >  (citext, citext),217    FUNCTION    1   citext_cmp(citext, citext);218 219--220-- The hash indexing operator class.221--222 223CREATE OPERATOR CLASS citext_ops224DEFAULT FOR TYPE citext USING hash AS225    OPERATOR    1   =  (citext, citext),226    FUNCTION    1   citext_hash(citext);227 228--229-- Aggregates.230--231 232CREATE FUNCTION citext_smaller(citext, citext)233RETURNS citext234AS 'MODULE_PATHNAME'235LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;236 237CREATE FUNCTION citext_larger(citext, citext)238RETURNS citext239AS 'MODULE_PATHNAME'240LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;241 242CREATE AGGREGATE min(citext)  (243    SFUNC = citext_smaller,244    STYPE = citext,245    SORTOP = <,246    PARALLEL = SAFE,247    COMBINEFUNC = citext_smaller248);249 250CREATE AGGREGATE max(citext)  (251    SFUNC = citext_larger,252    STYPE = citext,253    SORTOP = >,254    PARALLEL = SAFE,255    COMBINEFUNC = citext_larger256);257 258--259-- CITEXT pattern matching.260--261 262CREATE FUNCTION texticlike(citext, citext)263RETURNS bool AS 'texticlike'264LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;265 266CREATE FUNCTION texticnlike(citext, citext)267RETURNS bool AS 'texticnlike'268LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;269 270CREATE FUNCTION texticregexeq(citext, citext)271RETURNS bool AS 'texticregexeq'272LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;273 274CREATE FUNCTION texticregexne(citext, citext)275RETURNS bool AS 'texticregexne'276LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;277 278CREATE OPERATOR ~ (279    PROCEDURE = texticregexeq,280    LEFTARG   = citext,281    RIGHTARG  = citext,282    NEGATOR   = !~,283    RESTRICT  = icregexeqsel,284    JOIN      = icregexeqjoinsel285);286 287CREATE OPERATOR ~* (288    PROCEDURE = texticregexeq,289    LEFTARG   = citext,290    RIGHTARG  = citext,291    NEGATOR   = !~*,292    RESTRICT  = icregexeqsel,293    JOIN      = icregexeqjoinsel294);295 296CREATE OPERATOR !~ (297    PROCEDURE = texticregexne,298    LEFTARG   = citext,299    RIGHTARG  = citext,300    NEGATOR   = ~,301    RESTRICT  = icregexnesel,302    JOIN      = icregexnejoinsel303);304 305CREATE OPERATOR !~* (306    PROCEDURE = texticregexne,307    LEFTARG   = citext,308    RIGHTARG  = citext,309    NEGATOR   = ~*,310    RESTRICT  = icregexnesel,311    JOIN      = icregexnejoinsel312);313 314CREATE OPERATOR ~~ (315    PROCEDURE = texticlike,316    LEFTARG   = citext,317    RIGHTARG  = citext,318    NEGATOR   = !~~,319    RESTRICT  = iclikesel,320    JOIN      = iclikejoinsel321);322 323CREATE OPERATOR ~~* (324    PROCEDURE = texticlike,325    LEFTARG   = citext,326    RIGHTARG  = citext,327    NEGATOR   = !~~*,328    RESTRICT  = iclikesel,329    JOIN      = iclikejoinsel330);331 332CREATE OPERATOR !~~ (333    PROCEDURE = texticnlike,334    LEFTARG   = citext,335    RIGHTARG  = citext,336    NEGATOR   = ~~,337    RESTRICT  = icnlikesel,338    JOIN      = icnlikejoinsel339);340 341CREATE OPERATOR !~~* (342    PROCEDURE = texticnlike,343    LEFTARG   = citext,344    RIGHTARG  = citext,345    NEGATOR   = ~~*,346    RESTRICT  = icnlikesel,347    JOIN      = icnlikejoinsel348);349 350--351-- Matching citext to text.352--353 354CREATE FUNCTION texticlike(citext, text)355RETURNS bool AS 'texticlike'356LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;357 358CREATE FUNCTION texticnlike(citext, text)359RETURNS bool AS 'texticnlike'360LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;361 362CREATE FUNCTION texticregexeq(citext, text)363RETURNS bool AS 'texticregexeq'364LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;365 366CREATE FUNCTION texticregexne(citext, text)367RETURNS bool AS 'texticregexne'368LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;369 370CREATE OPERATOR ~ (371    PROCEDURE = texticregexeq,372    LEFTARG   = citext,373    RIGHTARG  = text,374    NEGATOR   = !~,375    RESTRICT  = icregexeqsel,376    JOIN      = icregexeqjoinsel377);378 379CREATE OPERATOR ~* (380    PROCEDURE = texticregexeq,381    LEFTARG   = citext,382    RIGHTARG  = text,383    NEGATOR   = !~*,384    RESTRICT  = icregexeqsel,385    JOIN      = icregexeqjoinsel386);387 388CREATE OPERATOR !~ (389    PROCEDURE = texticregexne,390    LEFTARG   = citext,391    RIGHTARG  = text,392    NEGATOR   = ~,393    RESTRICT  = icregexnesel,394    JOIN      = icregexnejoinsel395);396 397CREATE OPERATOR !~* (398    PROCEDURE = texticregexne,399    LEFTARG   = citext,400    RIGHTARG  = text,401    NEGATOR   = ~*,402    RESTRICT  = icregexnesel,403    JOIN      = icregexnejoinsel404);405 406CREATE OPERATOR ~~ (407    PROCEDURE = texticlike,408    LEFTARG   = citext,409    RIGHTARG  = text,410    NEGATOR   = !~~,411    RESTRICT  = iclikesel,412    JOIN      = iclikejoinsel413);414 415CREATE OPERATOR ~~* (416    PROCEDURE = texticlike,417    LEFTARG   = citext,418    RIGHTARG  = text,419    NEGATOR   = !~~*,420    RESTRICT  = iclikesel,421    JOIN      = iclikejoinsel422);423 424CREATE OPERATOR !~~ (425    PROCEDURE = texticnlike,426    LEFTARG   = citext,427    RIGHTARG  = text,428    NEGATOR   = ~~,429    RESTRICT  = icnlikesel,430    JOIN      = icnlikejoinsel431);432 433CREATE OPERATOR !~~* (434    PROCEDURE = texticnlike,435    LEFTARG   = citext,436    RIGHTARG  = text,437    NEGATOR   = ~~*,438    RESTRICT  = icnlikesel,439    JOIN      = icnlikejoinsel440);441 442--443-- Matching citext in string comparison functions.444-- XXX TODO Ideally these would be implemented in C.445--446 447CREATE FUNCTION regexp_match( citext, citext ) RETURNS TEXT[] AS $$448    SELECT pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );449$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;450 451CREATE FUNCTION regexp_match( citext, citext, text ) RETURNS TEXT[] AS $$452    SELECT pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );453$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;454 455CREATE FUNCTION regexp_matches( citext, citext ) RETURNS SETOF TEXT[] AS $$456    SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );457$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 1;458 459CREATE FUNCTION regexp_matches( citext, citext, text ) RETURNS SETOF TEXT[] AS $$460    SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );461$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 10;462 463CREATE FUNCTION regexp_replace( citext, citext, text ) returns TEXT AS $$464    SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, 'i');465$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;466 467CREATE FUNCTION regexp_replace( citext, citext, text, text ) returns TEXT AS $$468    SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, CASE WHEN pg_catalog.strpos($4, 'c') = 0 THEN  $4 || 'i' ELSE $4 END);469$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;470 471CREATE FUNCTION regexp_split_to_array( citext, citext ) RETURNS TEXT[] AS $$472    SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );473$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;474 475CREATE FUNCTION regexp_split_to_array( citext, citext, text ) RETURNS TEXT[] AS $$476    SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );477$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;478 479CREATE FUNCTION regexp_split_to_table( citext, citext ) RETURNS SETOF TEXT AS $$480    SELECT pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );481$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;482 483CREATE FUNCTION regexp_split_to_table( citext, citext, text ) RETURNS SETOF TEXT AS $$484    SELECT pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );485$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;486 487CREATE FUNCTION strpos( citext, citext ) RETURNS INT AS $$488    SELECT pg_catalog.strpos( pg_catalog.lower( $1::pg_catalog.text ), pg_catalog.lower( $2::pg_catalog.text ) );489$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;490 491CREATE FUNCTION replace( citext, citext, citext ) RETURNS TEXT AS $$492    SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), $3::pg_catalog.text, 'gi' );493$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;494 495CREATE FUNCTION split_part( citext, citext, int ) RETURNS TEXT AS $$496    SELECT (pg_catalog.regexp_split_to_array( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), 'i'))[$3];497$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;498 499CREATE FUNCTION translate( citext, citext, text ) RETURNS TEXT AS $$500    SELECT pg_catalog.translate( pg_catalog.translate( $1::pg_catalog.text, pg_catalog.lower($2::pg_catalog.text), $3), pg_catalog.upper($2::pg_catalog.text), $3);501$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;502