1/* contrib/citext/citext--1.4.sql */ 2 3-- complain if script is sourced in psql, rather than via CREATE EXTENSION 4\echo Use "CREATE EXTENSION citext" to load this file. \quit 5 6-- 7-- PostgreSQL code for CITEXT. 8-- 9-- Most I/O functions, and a few others, piggyback on the "text" type 10-- 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 citext 24AS 'textin' 25LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE; 26 27CREATE FUNCTION citextout(citext) 28RETURNS cstring 29AS 'textout' 30LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE; 31 32CREATE FUNCTION citextrecv(internal) 33RETURNS citext 34AS 'textrecv' 35LANGUAGE internal STABLE STRICT PARALLEL SAFE; 36 37CREATE FUNCTION citextsend(citext) 38RETURNS bytea 39AS '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 category 54 CATEGORY = 'S', 55 PREFERRED = false, 56 COLLATABLE = true 57); 58 59-- 60-- Type casting functions for those situations where the I/O casts don't 61-- automatically kick in. 62-- 63 64CREATE FUNCTION citext(bpchar) 65RETURNS citext 66AS 'rtrim1' 67LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE; 68 69CREATE FUNCTION citext(boolean) 70RETURNS citext 71AS 'booltext' 72LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE; 73 74CREATE FUNCTION citext(inet) 75RETURNS citext 76AS '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 bool 98AS 'MODULE_PATHNAME' 99LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 100 101CREATE FUNCTION citext_ne( citext, citext ) 102RETURNS bool 103AS 'MODULE_PATHNAME' 104LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 105 106CREATE FUNCTION citext_lt( citext, citext ) 107RETURNS bool 108AS 'MODULE_PATHNAME' 109LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 110 111CREATE FUNCTION citext_le( citext, citext ) 112RETURNS bool 113AS 'MODULE_PATHNAME' 114LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 115 116CREATE FUNCTION citext_gt( citext, citext ) 117RETURNS bool 118AS 'MODULE_PATHNAME' 119LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 120 121CREATE FUNCTION citext_ge( citext, citext ) 122RETURNS bool 123AS '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 MERGES 140); 141 142CREATE OPERATOR <> ( 143 LEFTARG = CITEXT, 144 RIGHTARG = CITEXT, 145 NEGATOR = =, 146 COMMUTATOR = <>, 147 PROCEDURE = citext_ne, 148 RESTRICT = neqsel, 149 JOIN = neqjoinsel 150); 151 152CREATE OPERATOR < ( 153 LEFTARG = CITEXT, 154 RIGHTARG = CITEXT, 155 NEGATOR = >=, 156 COMMUTATOR = >, 157 PROCEDURE = citext_lt, 158 RESTRICT = scalarltsel, 159 JOIN = scalarltjoinsel 160); 161 162CREATE OPERATOR <= ( 163 LEFTARG = CITEXT, 164 RIGHTARG = CITEXT, 165 NEGATOR = >, 166 COMMUTATOR = >=, 167 PROCEDURE = citext_le, 168 RESTRICT = scalarltsel, 169 JOIN = scalarltjoinsel 170); 171 172CREATE OPERATOR >= ( 173 LEFTARG = CITEXT, 174 RIGHTARG = CITEXT, 175 NEGATOR = <, 176 COMMUTATOR = <=, 177 PROCEDURE = citext_ge, 178 RESTRICT = scalargtsel, 179 JOIN = scalargtjoinsel 180); 181 182CREATE OPERATOR > ( 183 LEFTARG = CITEXT, 184 RIGHTARG = CITEXT, 185 NEGATOR = <=, 186 COMMUTATOR = <, 187 PROCEDURE = citext_gt, 188 RESTRICT = scalargtsel, 189 JOIN = scalargtjoinsel 190); 191 192-- 193-- Support functions for indexing. 194-- 195 196CREATE FUNCTION citext_cmp(citext, citext) 197RETURNS int4 198AS 'MODULE_PATHNAME' 199LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 200 201CREATE FUNCTION citext_hash(citext) 202RETURNS int4 203AS 'MODULE_PATHNAME' 204LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE; 205 206-- 207-- The btree indexing operator class. 208-- 209 210CREATE OPERATOR CLASS citext_ops 211DEFAULT FOR TYPE CITEXT USING btree AS 212 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_ops 224DEFAULT FOR TYPE citext USING hash AS 225 OPERATOR 1 = (citext, citext), 226 FUNCTION 1 citext_hash(citext); 227 228-- 229-- Aggregates. 230-- 231 232CREATE FUNCTION citext_smaller(citext, citext) 233RETURNS citext 234AS 'MODULE_PATHNAME' 235LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE; 236 237CREATE FUNCTION citext_larger(citext, citext) 238RETURNS citext 239AS '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_smaller 248); 249 250CREATE AGGREGATE max(citext) ( 251 SFUNC = citext_larger, 252 STYPE = citext, 253 SORTOP = >, 254 PARALLEL = SAFE, 255 COMBINEFUNC = citext_larger 256); 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 = icregexeqjoinsel 285); 286 287CREATE OPERATOR ~* ( 288 PROCEDURE = texticregexeq, 289 LEFTARG = citext, 290 RIGHTARG = citext, 291 NEGATOR = !~*, 292 RESTRICT = icregexeqsel, 293 JOIN = icregexeqjoinsel 294); 295 296CREATE OPERATOR !~ ( 297 PROCEDURE = texticregexne, 298 LEFTARG = citext, 299 RIGHTARG = citext, 300 NEGATOR = ~, 301 RESTRICT = icregexnesel, 302 JOIN = icregexnejoinsel 303); 304 305CREATE OPERATOR !~* ( 306 PROCEDURE = texticregexne, 307 LEFTARG = citext, 308 RIGHTARG = citext, 309 NEGATOR = ~*, 310 RESTRICT = icregexnesel, 311 JOIN = icregexnejoinsel 312); 313 314CREATE OPERATOR ~~ ( 315 PROCEDURE = texticlike, 316 LEFTARG = citext, 317 RIGHTARG = citext, 318 NEGATOR = !~~, 319 RESTRICT = iclikesel, 320 JOIN = iclikejoinsel 321); 322 323CREATE OPERATOR ~~* ( 324 PROCEDURE = texticlike, 325 LEFTARG = citext, 326 RIGHTARG = citext, 327 NEGATOR = !~~*, 328 RESTRICT = iclikesel, 329 JOIN = iclikejoinsel 330); 331 332CREATE OPERATOR !~~ ( 333 PROCEDURE = texticnlike, 334 LEFTARG = citext, 335 RIGHTARG = citext, 336 NEGATOR = ~~, 337 RESTRICT = icnlikesel, 338 JOIN = icnlikejoinsel 339); 340 341CREATE OPERATOR !~~* ( 342 PROCEDURE = texticnlike, 343 LEFTARG = citext, 344 RIGHTARG = citext, 345 NEGATOR = ~~*, 346 RESTRICT = icnlikesel, 347 JOIN = icnlikejoinsel 348); 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 = icregexeqjoinsel 377); 378 379CREATE OPERATOR ~* ( 380 PROCEDURE = texticregexeq, 381 LEFTARG = citext, 382 RIGHTARG = text, 383 NEGATOR = !~*, 384 RESTRICT = icregexeqsel, 385 JOIN = icregexeqjoinsel 386); 387 388CREATE OPERATOR !~ ( 389 PROCEDURE = texticregexne, 390 LEFTARG = citext, 391 RIGHTARG = text, 392 NEGATOR = ~, 393 RESTRICT = icregexnesel, 394 JOIN = icregexnejoinsel 395); 396 397CREATE OPERATOR !~* ( 398 PROCEDURE = texticregexne, 399 LEFTARG = citext, 400 RIGHTARG = text, 401 NEGATOR = ~*, 402 RESTRICT = icregexnesel, 403 JOIN = icregexnejoinsel 404); 405 406CREATE OPERATOR ~~ ( 407 PROCEDURE = texticlike, 408 LEFTARG = citext, 409 RIGHTARG = text, 410 NEGATOR = !~~, 411 RESTRICT = iclikesel, 412 JOIN = iclikejoinsel 413); 414 415CREATE OPERATOR ~~* ( 416 PROCEDURE = texticlike, 417 LEFTARG = citext, 418 RIGHTARG = text, 419 NEGATOR = !~~*, 420 RESTRICT = iclikesel, 421 JOIN = iclikejoinsel 422); 423 424CREATE OPERATOR !~~ ( 425 PROCEDURE = texticnlike, 426 LEFTARG = citext, 427 RIGHTARG = text, 428 NEGATOR = ~~, 429 RESTRICT = icnlikesel, 430 JOIN = icnlikejoinsel 431); 432 433CREATE OPERATOR !~~* ( 434 PROCEDURE = texticnlike, 435 LEFTARG = citext, 436 RIGHTARG = text, 437 NEGATOR = ~~*, 438 RESTRICT = icnlikesel, 439 JOIN = icnlikejoinsel 440); 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