Hi all,
The attached patch introduces citext_pattern_ops for citext extension
type like text_pattern_ops for text type. Here are operators ~<~, ~<=~,
~>~, ~>=~ combined into citext_pattern_ops operator class. These
operators simply compare underlying citext values as C strings with
memcmp() function. This operator class isn’t supported by B-Tree index
yet, but it is a first step to do it.
Patch includes regression tests and is applicable to the latest commit
(c85ec643ff2586e2d144374f51f93bfa215088a2).
The problem of citext support for LIKE operator with B-Tree index was
mentioned in [1]. Briefly, the planner doesn’t use B-Tree index for
queries text_col LIKE ‘abc%’. I’d like to investigate how to improve it
and make another patch. I think the start point is
match_special_index_operator() function which doesn’t support custom
types. I would appreciate hearing your opinion on this.
1. https://www.postgresql.org/message-id/3924.1480351187%40sss.pgh.pa.us
--
Alexey Chernyshov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
>From 6ff180548209fb3abb66d70686df728b307635e3 Mon Sep 17 00:00:00 2001
From: Alexey Chernyshov <a.chernys...@postgrespro.ru>
Date: Fri, 23 Jun 2017 14:40:04 +0300
Subject: [PATCH 1/3] add citext opclass citext_pattern_ops
---
contrib/citext/Makefile | 2 +-
contrib/citext/citext--1.4--1.5.sql | 1 +
contrib/citext/citext--1.4.sql | 501 ------------------------------
contrib/citext/citext--1.5.sql | 575 +++++++++++++++++++++++++++++++++++
contrib/citext/citext.c | 120 ++++++++
contrib/citext/citext.control | 2 +-
contrib/citext/expected/citext.out | 334 ++++++++++++++++++++
contrib/citext/expected/citext_1.out | 334 ++++++++++++++++++++
contrib/citext/sql/citext.sql | 72 +++++
9 files changed, 1438 insertions(+), 503 deletions(-)
create mode 100644 contrib/citext/citext--1.4--1.5.sql
delete mode 100644 contrib/citext/citext--1.4.sql
create mode 100644 contrib/citext/citext--1.5.sql
diff --git a/contrib/citext/Makefile b/contrib/citext/Makefile
index 563cd22..8474e86 100644
--- a/contrib/citext/Makefile
+++ b/contrib/citext/Makefile
@@ -3,7 +3,7 @@
MODULES = citext
EXTENSION = citext
-DATA = citext--1.4.sql citext--1.3--1.4.sql \
+DATA = citext--1.5.sql citext--1.4--1.5.sql citext--1.3--1.4.sql \
citext--1.2--1.3.sql citext--1.1--1.2.sql \
citext--1.0--1.1.sql citext--unpackaged--1.0.sql
PGFILEDESC = "citext - case-insensitive character string data type"
diff --git a/contrib/citext/citext--1.4--1.5.sql b/contrib/citext/citext--1.4--1.5.sql
new file mode 100644
index 0000000..4c8abc0
--- /dev/null
+++ b/contrib/citext/citext--1.4--1.5.sql
@@ -0,0 +1 @@
+/* contrib/citext/citext--1.4--1.5.sql */
diff --git a/contrib/citext/citext--1.4.sql b/contrib/citext/citext--1.4.sql
deleted file mode 100644
index 7b06198..0000000
--- a/contrib/citext/citext--1.4.sql
+++ /dev/null
@@ -1,501 +0,0 @@
-/* contrib/citext/citext--1.4.sql */
-
--- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use "CREATE EXTENSION citext" to load this file. \quit
-
---
--- PostgreSQL code for CITEXT.
---
--- Most I/O functions, and a few others, piggyback on the "text" type
--- functions via the implicit cast to text.
---
-
---
--- Shell type to keep things a bit quieter.
---
-
-CREATE TYPE citext;
-
---
--- Input and output functions.
---
-CREATE FUNCTION citextin(cstring)
-RETURNS citext
-AS 'textin'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION citextout(citext)
-RETURNS cstring
-AS 'textout'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION citextrecv(internal)
-RETURNS citext
-AS 'textrecv'
-LANGUAGE internal STABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION citextsend(citext)
-RETURNS bytea
-AS 'textsend'
-LANGUAGE internal STABLE STRICT PARALLEL SAFE;
-
---
--- The type itself.
---
-
-CREATE TYPE citext (
- INPUT = citextin,
- OUTPUT = citextout,
- RECEIVE = citextrecv,
- SEND = citextsend,
- INTERNALLENGTH = VARIABLE,
- STORAGE = extended,
- -- make it a non-preferred member of string type category
- CATEGORY = 'S',
- PREFERRED = false,
- COLLATABLE = true
-);
-
---
--- Type casting functions for those situations where the I/O casts don't
--- automatically kick in.
---
-
-CREATE FUNCTION citext(bpchar)
-RETURNS citext
-AS 'rtrim1'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION citext(boolean)
-RETURNS citext
-AS 'booltext'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION citext(inet)
-RETURNS citext
-AS 'network_show'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
---
--- Implicit and assignment type casts.
---
-
-CREATE CAST (citext AS text) WITHOUT FUNCTION AS IMPLICIT;
-CREATE CAST (citext AS varchar) WITHOUT FUNCTION AS IMPLICIT;
-CREATE CAST (citext AS bpchar) WITHOUT FUNCTION AS ASSIGNMENT;
-CREATE CAST (text AS citext) WITHOUT FUNCTION AS ASSIGNMENT;
-CREATE CAST (varchar AS citext) WITHOUT FUNCTION AS ASSIGNMENT;
-CREATE CAST (bpchar AS citext) WITH FUNCTION citext(bpchar) AS ASSIGNMENT;
-CREATE CAST (boolean AS citext) WITH FUNCTION citext(boolean) AS ASSIGNMENT;
-CREATE CAST (inet AS citext) WITH FUNCTION citext(inet) AS ASSIGNMENT;
-
---
--- Operator Functions.
---
-
-CREATE FUNCTION citext_eq( citext, citext )
-RETURNS bool
-AS 'MODULE_PATHNAME'
-LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION citext_ne( citext, citext )
-RETURNS bool
-AS 'MODULE_PATHNAME'
-LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION citext_lt( citext, citext )
-RETURNS bool
-AS 'MODULE_PATHNAME'
-LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION citext_le( citext, citext )
-RETURNS bool
-AS 'MODULE_PATHNAME'
-LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION citext_gt( citext, citext )
-RETURNS bool
-AS 'MODULE_PATHNAME'
-LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION citext_ge( citext, citext )
-RETURNS bool
-AS 'MODULE_PATHNAME'
-LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
-
---
--- Operators.
---
-
-CREATE OPERATOR = (
- LEFTARG = CITEXT,
- RIGHTARG = CITEXT,
- COMMUTATOR = =,
- NEGATOR = <>,
- PROCEDURE = citext_eq,
- RESTRICT = eqsel,
- JOIN = eqjoinsel,
- HASHES,
- MERGES
-);
-
-CREATE OPERATOR <> (
- LEFTARG = CITEXT,
- RIGHTARG = CITEXT,
- NEGATOR = =,
- COMMUTATOR = <>,
- PROCEDURE = citext_ne,
- RESTRICT = neqsel,
- JOIN = neqjoinsel
-);
-
-CREATE OPERATOR < (
- LEFTARG = CITEXT,
- RIGHTARG = CITEXT,
- NEGATOR = >=,
- COMMUTATOR = >,
- PROCEDURE = citext_lt,
- RESTRICT = scalarltsel,
- JOIN = scalarltjoinsel
-);
-
-CREATE OPERATOR <= (
- LEFTARG = CITEXT,
- RIGHTARG = CITEXT,
- NEGATOR = >,
- COMMUTATOR = >=,
- PROCEDURE = citext_le,
- RESTRICT = scalarltsel,
- JOIN = scalarltjoinsel
-);
-
-CREATE OPERATOR >= (
- LEFTARG = CITEXT,
- RIGHTARG = CITEXT,
- NEGATOR = <,
- COMMUTATOR = <=,
- PROCEDURE = citext_ge,
- RESTRICT = scalargtsel,
- JOIN = scalargtjoinsel
-);
-
-CREATE OPERATOR > (
- LEFTARG = CITEXT,
- RIGHTARG = CITEXT,
- NEGATOR = <=,
- COMMUTATOR = <,
- PROCEDURE = citext_gt,
- RESTRICT = scalargtsel,
- JOIN = scalargtjoinsel
-);
-
---
--- Support functions for indexing.
---
-
-CREATE FUNCTION citext_cmp(citext, citext)
-RETURNS int4
-AS 'MODULE_PATHNAME'
-LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
-
-CREATE FUNCTION citext_hash(citext)
-RETURNS int4
-AS 'MODULE_PATHNAME'
-LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
-
---
--- The btree indexing operator class.
---
-
-CREATE OPERATOR CLASS citext_ops
-DEFAULT FOR TYPE CITEXT USING btree AS
- OPERATOR 1 < (citext, citext),
- OPERATOR 2 <= (citext, citext),
- OPERATOR 3 = (citext, citext),
- OPERATOR 4 >= (citext, citext),
- OPERATOR 5 > (citext, citext),
- FUNCTION 1 citext_cmp(citext, citext);
-
---
--- The hash indexing operator class.
---
-
-CREATE OPERATOR CLASS citext_ops
-DEFAULT FOR TYPE citext USING hash AS
- OPERATOR 1 = (citext, citext),
- FUNCTION 1 citext_hash(citext);
-
---
--- Aggregates.
---
-
-CREATE FUNCTION citext_smaller(citext, citext)
-RETURNS citext
-AS 'MODULE_PATHNAME'
-LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION citext_larger(citext, citext)
-RETURNS citext
-AS 'MODULE_PATHNAME'
-LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE AGGREGATE min(citext) (
- SFUNC = citext_smaller,
- STYPE = citext,
- SORTOP = <,
- PARALLEL = SAFE,
- COMBINEFUNC = citext_smaller
-);
-
-CREATE AGGREGATE max(citext) (
- SFUNC = citext_larger,
- STYPE = citext,
- SORTOP = >,
- PARALLEL = SAFE,
- COMBINEFUNC = citext_larger
-);
-
---
--- CITEXT pattern matching.
---
-
-CREATE FUNCTION texticlike(citext, citext)
-RETURNS bool AS 'texticlike'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION texticnlike(citext, citext)
-RETURNS bool AS 'texticnlike'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION texticregexeq(citext, citext)
-RETURNS bool AS 'texticregexeq'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION texticregexne(citext, citext)
-RETURNS bool AS 'texticregexne'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE OPERATOR ~ (
- PROCEDURE = texticregexeq,
- LEFTARG = citext,
- RIGHTARG = citext,
- NEGATOR = !~,
- RESTRICT = icregexeqsel,
- JOIN = icregexeqjoinsel
-);
-
-CREATE OPERATOR ~* (
- PROCEDURE = texticregexeq,
- LEFTARG = citext,
- RIGHTARG = citext,
- NEGATOR = !~*,
- RESTRICT = icregexeqsel,
- JOIN = icregexeqjoinsel
-);
-
-CREATE OPERATOR !~ (
- PROCEDURE = texticregexne,
- LEFTARG = citext,
- RIGHTARG = citext,
- NEGATOR = ~,
- RESTRICT = icregexnesel,
- JOIN = icregexnejoinsel
-);
-
-CREATE OPERATOR !~* (
- PROCEDURE = texticregexne,
- LEFTARG = citext,
- RIGHTARG = citext,
- NEGATOR = ~*,
- RESTRICT = icregexnesel,
- JOIN = icregexnejoinsel
-);
-
-CREATE OPERATOR ~~ (
- PROCEDURE = texticlike,
- LEFTARG = citext,
- RIGHTARG = citext,
- NEGATOR = !~~,
- RESTRICT = iclikesel,
- JOIN = iclikejoinsel
-);
-
-CREATE OPERATOR ~~* (
- PROCEDURE = texticlike,
- LEFTARG = citext,
- RIGHTARG = citext,
- NEGATOR = !~~*,
- RESTRICT = iclikesel,
- JOIN = iclikejoinsel
-);
-
-CREATE OPERATOR !~~ (
- PROCEDURE = texticnlike,
- LEFTARG = citext,
- RIGHTARG = citext,
- NEGATOR = ~~,
- RESTRICT = icnlikesel,
- JOIN = icnlikejoinsel
-);
-
-CREATE OPERATOR !~~* (
- PROCEDURE = texticnlike,
- LEFTARG = citext,
- RIGHTARG = citext,
- NEGATOR = ~~*,
- RESTRICT = icnlikesel,
- JOIN = icnlikejoinsel
-);
-
---
--- Matching citext to text.
---
-
-CREATE FUNCTION texticlike(citext, text)
-RETURNS bool AS 'texticlike'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION texticnlike(citext, text)
-RETURNS bool AS 'texticnlike'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION texticregexeq(citext, text)
-RETURNS bool AS 'texticregexeq'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION texticregexne(citext, text)
-RETURNS bool AS 'texticregexne'
-LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE OPERATOR ~ (
- PROCEDURE = texticregexeq,
- LEFTARG = citext,
- RIGHTARG = text,
- NEGATOR = !~,
- RESTRICT = icregexeqsel,
- JOIN = icregexeqjoinsel
-);
-
-CREATE OPERATOR ~* (
- PROCEDURE = texticregexeq,
- LEFTARG = citext,
- RIGHTARG = text,
- NEGATOR = !~*,
- RESTRICT = icregexeqsel,
- JOIN = icregexeqjoinsel
-);
-
-CREATE OPERATOR !~ (
- PROCEDURE = texticregexne,
- LEFTARG = citext,
- RIGHTARG = text,
- NEGATOR = ~,
- RESTRICT = icregexnesel,
- JOIN = icregexnejoinsel
-);
-
-CREATE OPERATOR !~* (
- PROCEDURE = texticregexne,
- LEFTARG = citext,
- RIGHTARG = text,
- NEGATOR = ~*,
- RESTRICT = icregexnesel,
- JOIN = icregexnejoinsel
-);
-
-CREATE OPERATOR ~~ (
- PROCEDURE = texticlike,
- LEFTARG = citext,
- RIGHTARG = text,
- NEGATOR = !~~,
- RESTRICT = iclikesel,
- JOIN = iclikejoinsel
-);
-
-CREATE OPERATOR ~~* (
- PROCEDURE = texticlike,
- LEFTARG = citext,
- RIGHTARG = text,
- NEGATOR = !~~*,
- RESTRICT = iclikesel,
- JOIN = iclikejoinsel
-);
-
-CREATE OPERATOR !~~ (
- PROCEDURE = texticnlike,
- LEFTARG = citext,
- RIGHTARG = text,
- NEGATOR = ~~,
- RESTRICT = icnlikesel,
- JOIN = icnlikejoinsel
-);
-
-CREATE OPERATOR !~~* (
- PROCEDURE = texticnlike,
- LEFTARG = citext,
- RIGHTARG = text,
- NEGATOR = ~~*,
- RESTRICT = icnlikesel,
- JOIN = icnlikejoinsel
-);
-
---
--- Matching citext in string comparison functions.
--- XXX TODO Ideally these would be implemented in C.
---
-
-CREATE FUNCTION regexp_match( citext, citext ) RETURNS TEXT[] AS $$
- SELECT pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION regexp_match( citext, citext, text ) RETURNS TEXT[] AS $$
- 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 );
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION regexp_matches( citext, citext ) RETURNS SETOF TEXT[] AS $$
- SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 1;
-
-CREATE FUNCTION regexp_matches( citext, citext, text ) RETURNS SETOF TEXT[] AS $$
- 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 );
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 10;
-
-CREATE FUNCTION regexp_replace( citext, citext, text ) returns TEXT AS $$
- SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, 'i');
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION regexp_replace( citext, citext, text, text ) returns TEXT AS $$
- 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);
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION regexp_split_to_array( citext, citext ) RETURNS TEXT[] AS $$
- SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION regexp_split_to_array( citext, citext, text ) RETURNS TEXT[] AS $$
- 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 );
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION regexp_split_to_table( citext, citext ) RETURNS SETOF TEXT AS $$
- SELECT pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION regexp_split_to_table( citext, citext, text ) RETURNS SETOF TEXT AS $$
- 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 );
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION strpos( citext, citext ) RETURNS INT AS $$
- SELECT pg_catalog.strpos( pg_catalog.lower( $1::pg_catalog.text ), pg_catalog.lower( $2::pg_catalog.text ) );
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION replace( citext, citext, citext ) RETURNS TEXT AS $$
- 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' );
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION split_part( citext, citext, int ) RETURNS TEXT AS $$
- 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];
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
-
-CREATE FUNCTION translate( citext, citext, text ) RETURNS TEXT AS $$
- 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);
-$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
diff --git a/contrib/citext/citext--1.5.sql b/contrib/citext/citext--1.5.sql
new file mode 100644
index 0000000..047ff39
--- /dev/null
+++ b/contrib/citext/citext--1.5.sql
@@ -0,0 +1,575 @@
+/* contrib/citext/citext--1.5.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION citext" to load this file. \quit
+
+--
+-- PostgreSQL code for CITEXT.
+--
+-- Most I/O functions, and a few others, piggyback on the "text" type
+-- functions via the implicit cast to text.
+--
+
+--
+-- Shell type to keep things a bit quieter.
+--
+
+CREATE TYPE citext;
+
+--
+-- Input and output functions.
+--
+CREATE FUNCTION citextin(cstring)
+RETURNS citext
+AS 'textin'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citextout(citext)
+RETURNS cstring
+AS 'textout'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citextrecv(internal)
+RETURNS citext
+AS 'textrecv'
+LANGUAGE internal STABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citextsend(citext)
+RETURNS bytea
+AS 'textsend'
+LANGUAGE internal STABLE STRICT PARALLEL SAFE;
+
+--
+-- The type itself.
+--
+
+CREATE TYPE citext (
+ INPUT = citextin,
+ OUTPUT = citextout,
+ RECEIVE = citextrecv,
+ SEND = citextsend,
+ INTERNALLENGTH = VARIABLE,
+ STORAGE = extended,
+ -- make it a non-preferred member of string type category
+ CATEGORY = 'S',
+ PREFERRED = false,
+ COLLATABLE = true
+);
+
+--
+-- Type casting functions for those situations where the I/O casts don't
+-- automatically kick in.
+--
+
+CREATE FUNCTION citext(bpchar)
+RETURNS citext
+AS 'rtrim1'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext(boolean)
+RETURNS citext
+AS 'booltext'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext(inet)
+RETURNS citext
+AS 'network_show'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+--
+-- Implicit and assignment type casts.
+--
+
+CREATE CAST (citext AS text) WITHOUT FUNCTION AS IMPLICIT;
+CREATE CAST (citext AS varchar) WITHOUT FUNCTION AS IMPLICIT;
+CREATE CAST (citext AS bpchar) WITHOUT FUNCTION AS ASSIGNMENT;
+CREATE CAST (text AS citext) WITHOUT FUNCTION AS ASSIGNMENT;
+CREATE CAST (varchar AS citext) WITHOUT FUNCTION AS ASSIGNMENT;
+CREATE CAST (bpchar AS citext) WITH FUNCTION citext(bpchar) AS ASSIGNMENT;
+CREATE CAST (boolean AS citext) WITH FUNCTION citext(boolean) AS ASSIGNMENT;
+CREATE CAST (inet AS citext) WITH FUNCTION citext(inet) AS ASSIGNMENT;
+
+--
+-- Operator Functions.
+--
+
+CREATE FUNCTION citext_eq( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_ne( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_lt( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_le( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_gt( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_ge( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_pattern_lt( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_pattern_le( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_pattern_gt( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_pattern_ge( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+--
+-- Operators.
+--
+
+CREATE OPERATOR = (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ COMMUTATOR = =,
+ NEGATOR = <>,
+ PROCEDURE = citext_eq,
+ RESTRICT = eqsel,
+ JOIN = eqjoinsel,
+ HASHES,
+ MERGES
+);
+
+CREATE OPERATOR <> (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = =,
+ COMMUTATOR = <>,
+ PROCEDURE = citext_ne,
+ RESTRICT = neqsel,
+ JOIN = neqjoinsel
+);
+
+CREATE OPERATOR < (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = >=,
+ COMMUTATOR = >,
+ PROCEDURE = citext_lt,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+);
+
+CREATE OPERATOR <= (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = >,
+ COMMUTATOR = >=,
+ PROCEDURE = citext_le,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+);
+
+CREATE OPERATOR >= (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = <,
+ COMMUTATOR = <=,
+ PROCEDURE = citext_ge,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+);
+
+CREATE OPERATOR > (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = <=,
+ COMMUTATOR = <,
+ PROCEDURE = citext_gt,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+);
+
+CREATE OPERATOR ~<~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~>=~,
+ COMMUTATOR = ~>~,
+ PROCEDURE = citext_pattern_lt,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+);
+
+CREATE OPERATOR ~<=~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~>~,
+ COMMUTATOR = ~>=~,
+ PROCEDURE = citext_pattern_le,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+);
+
+CREATE OPERATOR ~>=~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~<~,
+ COMMUTATOR = ~<=~,
+ PROCEDURE = citext_pattern_ge,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+);
+
+CREATE OPERATOR ~>~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~<=~,
+ COMMUTATOR = ~<~,
+ PROCEDURE = citext_pattern_gt,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+);
+
+--
+-- Support functions for indexing.
+--
+
+CREATE FUNCTION citext_cmp(citext, citext)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
+
+CREATE FUNCTION citext_pattern_cmp(citext, citext)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
+
+CREATE FUNCTION citext_hash(citext)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
+
+--
+-- The btree indexing operator classes.
+--
+
+CREATE OPERATOR CLASS citext_ops
+DEFAULT FOR TYPE CITEXT USING btree AS
+ OPERATOR 1 < (citext, citext),
+ OPERATOR 2 <= (citext, citext),
+ OPERATOR 3 = (citext, citext),
+ OPERATOR 4 >= (citext, citext),
+ OPERATOR 5 > (citext, citext),
+ FUNCTION 1 citext_cmp(citext, citext);
+
+CREATE OPERATOR CLASS citext_pattern_ops
+FOR TYPE CITEXT USING btree AS
+ OPERATOR 1 ~<~ (citext, citext),
+ OPERATOR 2 ~<=~ (citext, citext),
+ OPERATOR 3 = (citext, citext),
+ OPERATOR 4 ~>=~ (citext, citext),
+ OPERATOR 5 ~>~ (citext, citext),
+ FUNCTION 1 citext_pattern_cmp(citext, citext);
+
+--
+-- The hash indexing operator class.
+--
+
+CREATE OPERATOR CLASS citext_ops
+DEFAULT FOR TYPE citext USING hash AS
+ OPERATOR 1 = (citext, citext),
+ FUNCTION 1 citext_hash(citext);
+
+--
+-- Aggregates.
+--
+
+CREATE FUNCTION citext_smaller(citext, citext)
+RETURNS citext
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_larger(citext, citext)
+RETURNS citext
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE AGGREGATE min(citext) (
+ SFUNC = citext_smaller,
+ STYPE = citext,
+ SORTOP = <,
+ PARALLEL = SAFE,
+ COMBINEFUNC = citext_smaller
+);
+
+CREATE AGGREGATE max(citext) (
+ SFUNC = citext_larger,
+ STYPE = citext,
+ SORTOP = >,
+ PARALLEL = SAFE,
+ COMBINEFUNC = citext_larger
+);
+
+--
+-- CITEXT pattern matching.
+--
+
+CREATE FUNCTION texticlike(citext, citext)
+RETURNS bool AS 'texticlike'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticnlike(citext, citext)
+RETURNS bool AS 'texticnlike'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticregexeq(citext, citext)
+RETURNS bool AS 'texticregexeq'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticregexne(citext, citext)
+RETURNS bool AS 'texticregexne'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE OPERATOR ~ (
+ PROCEDURE = texticregexeq,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = !~,
+ RESTRICT = icregexeqsel,
+ JOIN = icregexeqjoinsel
+);
+
+CREATE OPERATOR ~* (
+ PROCEDURE = texticregexeq,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = !~*,
+ RESTRICT = icregexeqsel,
+ JOIN = icregexeqjoinsel
+);
+
+CREATE OPERATOR !~ (
+ PROCEDURE = texticregexne,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = ~,
+ RESTRICT = icregexnesel,
+ JOIN = icregexnejoinsel
+);
+
+CREATE OPERATOR !~* (
+ PROCEDURE = texticregexne,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = ~*,
+ RESTRICT = icregexnesel,
+ JOIN = icregexnejoinsel
+);
+
+CREATE OPERATOR ~~ (
+ PROCEDURE = texticlike,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = !~~,
+ RESTRICT = iclikesel,
+ JOIN = iclikejoinsel
+);
+
+CREATE OPERATOR ~~* (
+ PROCEDURE = texticlike,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = !~~*,
+ RESTRICT = iclikesel,
+ JOIN = iclikejoinsel
+);
+
+CREATE OPERATOR !~~ (
+ PROCEDURE = texticnlike,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = ~~,
+ RESTRICT = icnlikesel,
+ JOIN = icnlikejoinsel
+);
+
+CREATE OPERATOR !~~* (
+ PROCEDURE = texticnlike,
+ LEFTARG = citext,
+ RIGHTARG = citext,
+ NEGATOR = ~~*,
+ RESTRICT = icnlikesel,
+ JOIN = icnlikejoinsel
+);
+
+--
+-- Matching citext to text.
+--
+
+CREATE FUNCTION texticlike(citext, text)
+RETURNS bool AS 'texticlike'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticnlike(citext, text)
+RETURNS bool AS 'texticnlike'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticregexeq(citext, text)
+RETURNS bool AS 'texticregexeq'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION texticregexne(citext, text)
+RETURNS bool AS 'texticregexne'
+LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE OPERATOR ~ (
+ PROCEDURE = texticregexeq,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = !~,
+ RESTRICT = icregexeqsel,
+ JOIN = icregexeqjoinsel
+);
+
+CREATE OPERATOR ~* (
+ PROCEDURE = texticregexeq,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = !~*,
+ RESTRICT = icregexeqsel,
+ JOIN = icregexeqjoinsel
+);
+
+CREATE OPERATOR !~ (
+ PROCEDURE = texticregexne,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = ~,
+ RESTRICT = icregexnesel,
+ JOIN = icregexnejoinsel
+);
+
+CREATE OPERATOR !~* (
+ PROCEDURE = texticregexne,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = ~*,
+ RESTRICT = icregexnesel,
+ JOIN = icregexnejoinsel
+);
+
+CREATE OPERATOR ~~ (
+ PROCEDURE = texticlike,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = !~~,
+ RESTRICT = iclikesel,
+ JOIN = iclikejoinsel
+);
+
+CREATE OPERATOR ~~* (
+ PROCEDURE = texticlike,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = !~~*,
+ RESTRICT = iclikesel,
+ JOIN = iclikejoinsel
+);
+
+CREATE OPERATOR !~~ (
+ PROCEDURE = texticnlike,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = ~~,
+ RESTRICT = icnlikesel,
+ JOIN = icnlikejoinsel
+);
+
+CREATE OPERATOR !~~* (
+ PROCEDURE = texticnlike,
+ LEFTARG = citext,
+ RIGHTARG = text,
+ NEGATOR = ~~*,
+ RESTRICT = icnlikesel,
+ JOIN = icnlikejoinsel
+);
+
+--
+-- Matching citext in string comparison functions.
+-- XXX TODO Ideally these would be implemented in C.
+--
+
+CREATE FUNCTION regexp_match( citext, citext ) RETURNS TEXT[] AS $$
+ SELECT pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_match( citext, citext, text ) RETURNS TEXT[] AS $$
+ 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 );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_matches( citext, citext ) RETURNS SETOF TEXT[] AS $$
+ SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 1;
+
+CREATE FUNCTION regexp_matches( citext, citext, text ) RETURNS SETOF TEXT[] AS $$
+ 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 );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 10;
+
+CREATE FUNCTION regexp_replace( citext, citext, text ) returns TEXT AS $$
+ SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, 'i');
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_replace( citext, citext, text, text ) returns TEXT AS $$
+ 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);
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_split_to_array( citext, citext ) RETURNS TEXT[] AS $$
+ SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_split_to_array( citext, citext, text ) RETURNS TEXT[] AS $$
+ 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 );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_split_to_table( citext, citext ) RETURNS SETOF TEXT AS $$
+ SELECT pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION regexp_split_to_table( citext, citext, text ) RETURNS SETOF TEXT AS $$
+ 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 );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION strpos( citext, citext ) RETURNS INT AS $$
+ SELECT pg_catalog.strpos( pg_catalog.lower( $1::pg_catalog.text ), pg_catalog.lower( $2::pg_catalog.text ) );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION replace( citext, citext, citext ) RETURNS TEXT AS $$
+ 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' );
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION split_part( citext, citext, int ) RETURNS TEXT AS $$
+ 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];
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION translate( citext, citext, text ) RETURNS TEXT AS $$
+ 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);
+$$ LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;
diff --git a/contrib/citext/citext.c b/contrib/citext/citext.c
index 04f604b..928dcf2 100644
--- a/contrib/citext/citext.c
+++ b/contrib/citext/citext.c
@@ -20,6 +20,7 @@ PG_MODULE_MAGIC;
*/
static int32 citextcmp(text *left, text *right, Oid collid);
+static int32 internal_citext_pattern_cmp(text *left, text *right, Oid collid);
/*
* =================
@@ -61,6 +62,40 @@ citextcmp(text *left, text *right, Oid collid)
}
/*
+ * citext_pattern_cmp()
+ * Internal character-by-character comparison function for citext strings.
+ * Returns int32 negative, zero, or positive.
+ */
+static int32
+internal_citext_pattern_cmp(text *left, text *right, Oid collid)
+{
+ char *lcstr,
+ *rcstr;
+ int llen,
+ rlen;
+ int32 result;
+
+ lcstr = str_tolower(VARDATA_ANY(left), VARSIZE_ANY_EXHDR(left), DEFAULT_COLLATION_OID);
+ rcstr = str_tolower(VARDATA_ANY(right), VARSIZE_ANY_EXHDR(right), DEFAULT_COLLATION_OID);
+
+ llen = strlen(lcstr);
+ rlen = strlen(rcstr);
+
+ result = memcmp((void *) lcstr, (void *) rcstr, Min(llen, rlen));
+ if (result == 0) {
+ if (llen < rlen)
+ result = -1;
+ else if (llen > rlen)
+ result = 1;
+ }
+
+ pfree(lcstr);
+ pfree(rcstr);
+
+ return result;
+}
+
+/*
* ==================
* INDEXING FUNCTIONS
* ==================
@@ -83,6 +118,23 @@ citext_cmp(PG_FUNCTION_ARGS)
PG_RETURN_INT32(result);
}
+PG_FUNCTION_INFO_V1(citext_pattern_cmp);
+
+Datum
+citext_pattern_cmp(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ int32 result;
+
+ result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION());
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 1);
+
+ PG_RETURN_INT32(result);
+}
+
PG_FUNCTION_INFO_V1(citext_hash);
Datum
@@ -236,6 +288,74 @@ citext_ge(PG_FUNCTION_ARGS)
PG_RETURN_BOOL(result);
}
+PG_FUNCTION_INFO_V1(citext_pattern_lt);
+
+Datum
+citext_pattern_lt(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) < 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 0);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_pattern_le);
+
+Datum
+citext_pattern_le(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) <= 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 0);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_pattern_gt);
+
+Datum
+citext_pattern_gt(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) > 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 0);
+
+ PG_RETURN_BOOL(result);
+}
+
+PG_FUNCTION_INFO_V1(citext_pattern_ge);
+
+Datum
+citext_pattern_ge(PG_FUNCTION_ARGS)
+{
+ text *left = PG_GETARG_TEXT_PP(0);
+ text *right = PG_GETARG_TEXT_PP(1);
+ bool result;
+
+ result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) >= 0;
+
+ PG_FREE_IF_COPY(left, 0);
+ PG_FREE_IF_COPY(right, 0);
+
+ PG_RETURN_BOOL(result);
+}
+
/*
* ===================
* AGGREGATE FUNCTIONS
diff --git a/contrib/citext/citext.control b/contrib/citext/citext.control
index 17fce4e..4cd6e09 100644
--- a/contrib/citext/citext.control
+++ b/contrib/citext/citext.control
@@ -1,5 +1,5 @@
# citext extension
comment = 'data type for case-insensitive character strings'
-default_version = '1.4'
+default_version = '1.5'
module_pathname = '$libdir/citext'
relocatable = true
diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out
index 9cc94f4..8a3f845 100644
--- a/contrib/citext/expected/citext.out
+++ b/contrib/citext/expected/citext.out
@@ -2351,3 +2351,337 @@ SELECT * FROM citext_matview ORDER BY id;
5 |
(5 rows)
+-- test citext_pattern_cmp() function explicetily.
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardVark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('B'::citext, 'a'::citext) > 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('a'::citext, 'B'::citext) < 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('A'::citext, 'b'::citext) < 0 AS true;
+ true
+------
+ t
+(1 row)
+
+-- test operator functions
+-- lt
+SELECT citext_pattern_lt('a'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('A'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('b'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_lt('B'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_lt('b'::citext, 'A'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+-- le
+SELECT citext_pattern_le('a'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('b'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_le('B'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_le('b'::citext, 'A'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+-- gt
+SELECT citext_pattern_gt('a'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('A'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('b'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_gt('B'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_gt('b'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+-- ge
+SELECT citext_pattern_ge('a'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('b'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('B'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('b'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+-- Test ~<~ and ~<=~
+SELECT 'a'::citext ~<~ 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'b'::citext ~<~ 'A'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'a'::citext ~<=~ 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~<=~ 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test ~>~ and ~>=~
+SELECT 'B'::citext ~>~ 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'b'::citext ~>~ 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~>~ 'b'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'B'::citext ~>=~ 'b'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test implicit casting. citext casts to text, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~<=~ 'a'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>~ 'B'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>=~ 'B'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+-- Test implicit casting. citext casts to varchar, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~<=~ 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>~ 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>=~ 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
diff --git a/contrib/citext/expected/citext_1.out b/contrib/citext/expected/citext_1.out
index d1fb1e1..1770439 100644
--- a/contrib/citext/expected/citext_1.out
+++ b/contrib/citext/expected/citext_1.out
@@ -2351,3 +2351,337 @@ SELECT * FROM citext_matview ORDER BY id;
5 |
(5 rows)
+-- test citext_pattern_cmp() function explicetily.
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardVark'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero;
+ zero
+------
+ 0
+(1 row)
+
+SELECT citext_pattern_cmp('B'::citext, 'a'::citext) > 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('a'::citext, 'B'::citext) < 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('A'::citext, 'b'::citext) < 0 AS true;
+ true
+------
+ t
+(1 row)
+
+-- test operator functions
+-- lt
+SELECT citext_pattern_lt('a'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('A'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_lt('b'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_lt('B'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_lt('b'::citext, 'A'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+-- le
+SELECT citext_pattern_le('a'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('A'::citext, 'b'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_le('b'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_le('B'::citext, 'a'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_le('b'::citext, 'A'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+-- gt
+SELECT citext_pattern_gt('a'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('A'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_gt('b'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_gt('B'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_gt('b'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+-- ge
+SELECT citext_pattern_ge('a'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('A'::citext, 'b'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+ false
+-------
+ f
+(1 row)
+
+SELECT citext_pattern_ge('b'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('B'::citext, 'a'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_ge('b'::citext, 'A'::citext) AS true;
+ true
+------
+ t
+(1 row)
+
+-- Test ~<~ and ~<=~
+SELECT 'a'::citext ~<~ 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'b'::citext ~<~ 'A'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'a'::citext ~<=~ 'B'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~<=~ 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test ~>~ and ~>=~
+SELECT 'B'::citext ~>~ 'a'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'b'::citext ~>~ 'A'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~>~ 'b'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'B'::citext ~>=~ 'b'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+-- Test implicit casting. citext casts to text, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~<=~ 'a'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>~ 'B'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>=~ 'B'::text AS t; -- text wins.
+ t
+---
+ t
+(1 row)
+
+-- Test implicit casting. citext casts to varchar, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'B'::citext ~<=~ 'a'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>~ 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
+SELECT 'a'::citext ~>=~ 'B'::varchar AS t; -- varchar wins.
+ t
+---
+ t
+(1 row)
+
diff --git a/contrib/citext/sql/citext.sql b/contrib/citext/sql/citext.sql
index f70f9eb..087505b 100644
--- a/contrib/citext/sql/citext.sql
+++ b/contrib/citext/sql/citext.sql
@@ -752,3 +752,75 @@ SELECT *
WHERE t.id IS NULL OR m.id IS NULL;
REFRESH MATERIALIZED VIEW CONCURRENTLY citext_matview;
SELECT * FROM citext_matview ORDER BY id;
+
+-- test citext_pattern_cmp() function explicetily.
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardvark'::citext) AS zero;
+SELECT citext_pattern_cmp('aardvark'::citext, 'aardVark'::citext) AS zero;
+SELECT citext_pattern_cmp('AARDVARK'::citext, 'AARDVARK'::citext) AS zero;
+SELECT citext_pattern_cmp('B'::citext, 'a'::citext) > 0 AS true;
+SELECT citext_pattern_cmp('a'::citext, 'B'::citext) < 0 AS true;
+SELECT citext_pattern_cmp('A'::citext, 'b'::citext) < 0 AS true;
+
+-- test operator functions
+-- lt
+SELECT citext_pattern_lt('a'::citext, 'b'::citext) AS true;
+SELECT citext_pattern_lt('A'::citext, 'b'::citext) AS true;
+SELECT citext_pattern_lt('a'::citext, 'B'::citext) AS true;
+SELECT citext_pattern_lt('b'::citext, 'a'::citext) AS false;
+SELECT citext_pattern_lt('B'::citext, 'a'::citext) AS false;
+SELECT citext_pattern_lt('b'::citext, 'A'::citext) AS false;
+-- le
+SELECT citext_pattern_le('a'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_le('a'::citext, 'A'::citext) AS true;
+SELECT citext_pattern_le('A'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_le('A'::citext, 'A'::citext) AS true;
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+SELECT citext_pattern_le('A'::citext, 'b'::citext) AS true;
+SELECT citext_pattern_le('a'::citext, 'B'::citext) AS true;
+SELECT citext_pattern_le('b'::citext, 'a'::citext) AS false;
+SELECT citext_pattern_le('B'::citext, 'a'::citext) AS false;
+SELECT citext_pattern_le('b'::citext, 'A'::citext) AS false;
+-- gt
+SELECT citext_pattern_gt('a'::citext, 'b'::citext) AS false;
+SELECT citext_pattern_gt('A'::citext, 'b'::citext) AS false;
+SELECT citext_pattern_gt('a'::citext, 'B'::citext) AS false;
+SELECT citext_pattern_gt('b'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_gt('B'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_gt('b'::citext, 'A'::citext) AS true;
+-- ge
+SELECT citext_pattern_ge('a'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_ge('a'::citext, 'A'::citext) AS true;
+SELECT citext_pattern_ge('A'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_ge('A'::citext, 'A'::citext) AS true;
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+SELECT citext_pattern_ge('A'::citext, 'b'::citext) AS false;
+SELECT citext_pattern_ge('a'::citext, 'B'::citext) AS false;
+SELECT citext_pattern_ge('b'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_ge('B'::citext, 'a'::citext) AS true;
+SELECT citext_pattern_ge('b'::citext, 'A'::citext) AS true;
+
+-- Test ~<~ and ~<=~
+SELECT 'a'::citext ~<~ 'B'::citext AS t;
+SELECT 'b'::citext ~<~ 'A'::citext AS f;
+SELECT 'a'::citext ~<=~ 'B'::citext AS t;
+SELECT 'a'::citext ~<=~ 'A'::citext AS t;
+
+-- Test ~>~ and ~>=~
+SELECT 'B'::citext ~>~ 'a'::citext AS t;
+SELECT 'b'::citext ~>~ 'A'::citext AS t;
+SELECT 'B'::citext ~>~ 'b'::citext AS f;
+SELECT 'B'::citext ~>=~ 'b'::citext AS t;
+
+-- Test implicit casting. citext casts to text, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::text AS t; -- text wins.
+SELECT 'B'::citext ~<=~ 'a'::text AS t; -- text wins.
+
+SELECT 'a'::citext ~>~ 'B'::text AS t; -- text wins.
+SELECT 'a'::citext ~>=~ 'B'::text AS t; -- text wins.
+
+-- Test implicit casting. citext casts to varchar, but not vice-versa.
+SELECT 'B'::citext ~<~ 'a'::varchar AS t; -- varchar wins.
+SELECT 'B'::citext ~<=~ 'a'::varchar AS t; -- varchar wins.
+
+SELECT 'a'::citext ~>~ 'B'::varchar AS t; -- varchar wins.
+SELECT 'a'::citext ~>=~ 'B'::varchar AS t; -- varchar wins.
--
2.7.4
>From 9d99aa3f75ab6a29a18f885a736ae4887c02c839 Mon Sep 17 00:00:00 2001
From: Alexey Chernyshov <a.chernys...@postgrespro.ru>
Date: Mon, 26 Jun 2017 09:55:57 +0300
Subject: [PATCH 2/3] tab fix
---
contrib/citext/citext.c | 8 ++++----
1 file changed, 4 insertions(+), 4 deletions(-)
diff --git a/contrib/citext/citext.c b/contrib/citext/citext.c
index 928dcf2..8d4c64d 100644
--- a/contrib/citext/citext.c
+++ b/contrib/citext/citext.c
@@ -300,7 +300,7 @@ citext_pattern_lt(PG_FUNCTION_ARGS)
result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) < 0;
PG_FREE_IF_COPY(left, 0);
- PG_FREE_IF_COPY(right, 0);
+ PG_FREE_IF_COPY(right, 1);
PG_RETURN_BOOL(result);
}
@@ -317,7 +317,7 @@ citext_pattern_le(PG_FUNCTION_ARGS)
result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) <= 0;
PG_FREE_IF_COPY(left, 0);
- PG_FREE_IF_COPY(right, 0);
+ PG_FREE_IF_COPY(right, 1);
PG_RETURN_BOOL(result);
}
@@ -334,7 +334,7 @@ citext_pattern_gt(PG_FUNCTION_ARGS)
result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) > 0;
PG_FREE_IF_COPY(left, 0);
- PG_FREE_IF_COPY(right, 0);
+ PG_FREE_IF_COPY(right, 1);
PG_RETURN_BOOL(result);
}
@@ -351,7 +351,7 @@ citext_pattern_ge(PG_FUNCTION_ARGS)
result = internal_citext_pattern_cmp(left, right, PG_GET_COLLATION()) >= 0;
PG_FREE_IF_COPY(left, 0);
- PG_FREE_IF_COPY(right, 0);
+ PG_FREE_IF_COPY(right, 1);
PG_RETURN_BOOL(result);
}
--
2.7.4
>From a3da8a23e65748ffe87b41c1a32b0c56b19c7d91 Mon Sep 17 00:00:00 2001
From: Alexey Chernyshov <a.chernys...@postgrespro.ru>
Date: Wed, 28 Jun 2017 17:13:03 +0300
Subject: [PATCH 3/3] add migration 1.4--1.5 script
---
contrib/citext/citext--1.4--1.5.sql | 77 +++++++++++++++++++++++++++++++++++++
1 file changed, 77 insertions(+)
diff --git a/contrib/citext/citext--1.4--1.5.sql b/contrib/citext/citext--1.4--1.5.sql
index 4c8abc0..f7ce9a2 100644
--- a/contrib/citext/citext--1.4--1.5.sql
+++ b/contrib/citext/citext--1.4--1.5.sql
@@ -1 +1,78 @@
/* contrib/citext/citext--1.4--1.5.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.5'" to load this file. \quit
+
+CREATE FUNCTION citext_pattern_lt( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_pattern_le( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_pattern_gt( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE FUNCTION citext_pattern_ge( citext, citext )
+RETURNS bool
+AS 'MODULE_PATHNAME'
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE;
+
+CREATE OPERATOR ~<~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~>=~,
+ COMMUTATOR = ~>~,
+ PROCEDURE = citext_pattern_lt,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+);
+
+CREATE OPERATOR ~<=~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~>~,
+ COMMUTATOR = ~>=~,
+ PROCEDURE = citext_pattern_le,
+ RESTRICT = scalarltsel,
+ JOIN = scalarltjoinsel
+);
+
+CREATE OPERATOR ~>=~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~<~,
+ COMMUTATOR = ~<=~,
+ PROCEDURE = citext_pattern_ge,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+);
+
+CREATE OPERATOR ~>~ (
+ LEFTARG = CITEXT,
+ RIGHTARG = CITEXT,
+ NEGATOR = ~<=~,
+ COMMUTATOR = ~<~,
+ PROCEDURE = citext_pattern_gt,
+ RESTRICT = scalargtsel,
+ JOIN = scalargtjoinsel
+);
+
+CREATE FUNCTION citext_pattern_cmp(citext, citext)
+RETURNS int4
+AS 'MODULE_PATHNAME'
+LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
+
+CREATE OPERATOR CLASS citext_pattern_ops
+FOR TYPE CITEXT USING btree AS
+ OPERATOR 1 ~<~ (citext, citext),
+ OPERATOR 2 ~<=~ (citext, citext),
+ OPERATOR 3 = (citext, citext),
+ OPERATOR 4 ~>=~ (citext, citext),
+ OPERATOR 5 ~>~ (citext, citext),
+ FUNCTION 1 citext_pattern_cmp(citext, citext);
--
2.7.4
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers