On Tue, 12 Sep 2017 12:59:20 -0400
Tom Lane <t...@sss.pgh.pa.us> wrote:
> Quick comment on this patch: recently, we've decided that having
> patches replace the whole base script for an extension is too much of
> a maintenance problem, especially when there are several patches in
> the pipeline for the same contrib module. The new style is to
> provide only a version update script (which you'd have to write
> anyway), and then rely on CREATE EXTENSION to apply the old base
> script plus the update(s). You can see some examples in the patch I
> just posted at
>
> https://www.postgresql.org/message-id/24721.1505229...@sss.pgh.pa.us
>
> Also, since that patch is probably going to get committed pretty
> soon, you could reformulate your patch as an add-on to its
> citext--1.4--1.5.sql script. We don't really need to have a separate
> version of the extension for states that are intermediate between two
> PG major releases. Only if your patch doesn't get in by v11 freeze
> would you need to make it a separate citext--1.5--1.6.sql script.
>
> regards, tom lane
Accented characters and different length strings tests added.
Since patch
(ttps://www.postgresql.org/message-id/24721.1505229...@sss.pgh.pa.us)
is committed, I changed the patch as you said. Thanks for your notes.
Do we need expected/citext.out? It seems that only
expected/citext_1.out has correct output.
--
Alexey Chernyshov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
From 35aa8d7a1890a242cdfb3bed6cabaa3b39766f1f Mon Sep 17 00:00:00 2001
From: Alexey Chernyshov <a.chernys...@postgrespro.ru>
Date: Tue, 18 Jul 2017 13:50:19 +0300
Subject: [PATCH] patch applied
---
contrib/citext/citext--1.4--1.5.sql | 74 +++++++
contrib/citext/citext.c | 120 ++++++++++++
contrib/citext/expected/citext.out | 370 +++++++++++++++++++++++++++++++++++
contrib/citext/expected/citext_1.out | 370 +++++++++++++++++++++++++++++++++++
contrib/citext/sql/citext.sql | 78 ++++++++
5 files changed, 1012 insertions(+)
diff --git a/contrib/citext/citext--1.4--1.5.sql b/contrib/citext/citext--1.4--1.5.sql
index 97942cb..5ae522b 100644
--- a/contrib/citext/citext--1.4--1.5.sql
+++ b/contrib/citext/citext--1.4--1.5.sql
@@ -12,3 +12,77 @@ ALTER OPERATOR >= (citext, citext) SET (
RESTRICT = scalargesel,
JOIN = scalargejoinsel
);
+
+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);
diff --git a/contrib/citext/citext.c b/contrib/citext/citext.c
index 0ba4782..189105a 100644
--- a/contrib/citext/citext.c
+++ b/contrib/citext/citext.c
@@ -18,6 +18,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);
/*
* =================
@@ -59,6 +60,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
* ==================
@@ -81,6 +116,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
@@ -234,6 +286,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, 1);
+
+ 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, 1);
+
+ 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, 1);
+
+ 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, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
/*
* ===================
* AGGREGATE FUNCTIONS
diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out
index 9cc94f4..c5bfda2 100644
--- a/contrib/citext/expected/citext.out
+++ b/contrib/citext/expected/citext.out
@@ -2351,3 +2351,373 @@ SELECT * FROM citext_matview ORDER BY id;
5 |
(5 rows)
+-- test citext_pattern_cmp() function explicitly.
+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)
+
+SELECT citext_pattern_cmp('ABCD'::citext, 'abc'::citext) > 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('ABC'::citext, 'abcd'::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 'Ã '::citext ~<~ 'Ã'::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)
+
+SELECT 'Ã '::citext ~<=~ 'Ã'::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 'Ã '::citext ~>~ 'Ã'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'B'::citext ~>~ 'b'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'B'::citext ~>=~ 'b'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'Ã '::citext ~>=~ 'Ã'::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..95549c5 100644
--- a/contrib/citext/expected/citext_1.out
+++ b/contrib/citext/expected/citext_1.out
@@ -2351,3 +2351,373 @@ SELECT * FROM citext_matview ORDER BY id;
5 |
(5 rows)
+-- test citext_pattern_cmp() function explicitly.
+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)
+
+SELECT citext_pattern_cmp('ABCD'::citext, 'abc'::citext) > 0 AS true;
+ true
+------
+ t
+(1 row)
+
+SELECT citext_pattern_cmp('ABC'::citext, 'abcd'::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 'Ã '::citext ~<~ 'Ã'::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)
+
+SELECT 'Ã '::citext ~<=~ 'Ã'::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 'Ã '::citext ~>~ 'Ã'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'B'::citext ~>~ 'b'::citext AS f;
+ f
+---
+ f
+(1 row)
+
+SELECT 'B'::citext ~>=~ 'b'::citext AS t;
+ t
+---
+ t
+(1 row)
+
+SELECT 'Ã '::citext ~>=~ 'Ã'::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..e9acd46 100644
--- a/contrib/citext/sql/citext.sql
+++ b/contrib/citext/sql/citext.sql
@@ -752,3 +752,81 @@ 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 explicitly.
+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;
+SELECT citext_pattern_cmp('ABCD'::citext, 'abc'::citext) > 0 AS true;
+SELECT citext_pattern_cmp('ABC'::citext, 'abcd'::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 'Ã '::citext ~<~ 'Ã'::citext AS f;
+SELECT 'a'::citext ~<=~ 'B'::citext AS t;
+SELECT 'a'::citext ~<=~ 'A'::citext AS t;
+SELECT 'Ã '::citext ~<=~ 'Ã'::citext AS t;
+
+-- Test ~>~ and ~>=~
+SELECT 'B'::citext ~>~ 'a'::citext AS t;
+SELECT 'b'::citext ~>~ 'A'::citext AS t;
+SELECT 'Ã '::citext ~>~ 'Ã'::citext AS f;
+SELECT 'B'::citext ~>~ 'b'::citext AS f;
+SELECT 'B'::citext ~>=~ 'b'::citext AS t;
+SELECT 'Ã '::citext ~>=~ 'Ã'::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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
