wake-up-neo.com

PostgreSQL: Vergleich von Strings ohne Berücksichtigung der Groß- und Kleinschreibung

Gibt es einen einfachen Groß-/Kleinschreibung-Vergleich für PostgreSQL?

Ich möchte ersetzen:

SELECT id, user_name 
    FROM users 
        WHERE lower(email) IN (lower('[email protected]'), lower('[email protected]'));

Mit etwas wie:

SELECT id, user_name 
    FROM users 
        WHERE email IGNORE_CASE_IN ('[email protected]', '[email protected]');

Die Operatoren like und ilike bearbeiten einzelne Werte (z. B. like '[email protected]'), aber nicht am Set.

41
Adam Matan

Erstens, was nicht zu tun ist, nicht gleich verwenden ...

create table y
(
id serial not null,
email text not null unique
);

insert into y(email) 
values('[email protected]') ,('[email protected]');
insert into y(email) 
select n from generate_series(1,1000) as i(n);
create index ix_y on y(email);

explain select * from y 
where email ilike 
    ANY(ARRAY['[email protected]','[email protected]']);

Ausführungsplan:

memdb=# explain select * from y where email ilike ANY(ARRAY['[email protected]','[email protected]']);
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Seq Scan on y  (cost=0.00..17.52 rows=1 width=7)
   Filter: (email ~~* ANY ('{[email protected],[email protected]}'::text[]))
(2 rows)

Entweder Sie erstellen einen indizierten unteren Ausdruck ...

create function lower(t text[]) returns text[]
as
$$
select lower($1::text)::text[]
$$ language sql;

create unique index ix_y_2 on y(lower(email));

explain select * from y 
where lower(email) = 
    ANY(lower(ARRAY['[email protected]','[email protected]']));

... die den Index richtig benutzt:

memdb=# explain select * from y where lower(email) = ANY(lower(ARRAY['[email protected]','[email protected]']));
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on y  (cost=22.60..27.98 rows=10 width=7)
   Recheck Cond: (lower(email) = ANY ((lower(('{[email protected],[email protected]}'::text[])::text))::text[]))
   ->  Bitmap Index Scan on ix_y_2  (cost=0.00..22.60 rows=10 width=0)
         Index Cond: (lower(email) = ANY ((lower(('{[email protected],[email protected]}'::text[])::text))::text[]))
(4 rows)

Oder Sie verwenden den citext-Datentyp ...

create table x
(
id serial not null,
email citext not null unique
);

insert into x(email) 
values('[email protected]'),('[email protected]');
insert into x(email) 
select n from generate_series(1,1000) as i(n);
create index ix_x on x(email);

explain select * from x 
where email = 
ANY(ARRAY['[email protected]','[email protected]']::citext[]);

... der den Index richtig verwendet, auch wenn Sie keinen Index für den Ausdruck erstellen (z. B. Index zzz für JJJ (unteres (Feld)):

memdb=# explain select * from x where email = ANY(ARRAY['[email protected]','[email protected]']::citext[]);
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on x  (cost=8.52..12.75 rows=2 width=7)
   Recheck Cond: (email = ANY ('{[email protected],[email protected]}'::citext[]))
   ->  Bitmap Index Scan on ix_x  (cost=0.00..8.52 rows=2 width=0)
         Index Cond: (email = ANY ('{[email protected],[email protected]}'::citext[]))
(4 rows)
19
Michael Buen
select * 
where email ilike '[email protected]'

ilike ähnelt like, unterscheidet jedoch nicht zwischen Groß- und Kleinschreibung. Verwenden Sie als Escape-Zeichen replace()

where email ilike replace(replace(replace($1, '~', '~~'), '%', '~%'), '_', '~_') escape '~'

oder Sie können eine Funktion erstellen, um Text zu maskieren. für Array von Text verwenden

where email ilike any(array['[email protected]', '[email protected]'])
44
Bonshington

Die Dinge haben sich in den letzten 4 Jahren geändert, seit diese Frage beantwortet wurde und die Empfehlung "ILIKE nicht verwenden" nicht mehr zutrifft (zumindest so allgemein).

Abhängig von der Datenverteilung ist ILIKE mit einem Trigrammindex sogar schneller als citext.

Für einen eindeutigen Index gibt es in der Tat einen großen Unterschied, der sich bei der Verwendung von Michaels Testaufbau zeigt:

create table y
(
  id serial not null,
  email text not null unique
);

insert into y(email) 
select 'some.name'||n||'@foobar.com'
from generate_series(1,100000) as i(n);

-- create a trigram index to support ILIKE    
create index ix_y on y using gin (email gin_trgm_ops);

create table x
(
  id serial not null,
  email citext not null unique
);
-- no need to create an index
-- the UNIQUE constraint will create a regular B-Tree index

insert into x(email) 
select email
from y;

Der Ausführungsplan für die Verwendung von ILIKE:

explain (analyze)
select * 
from y 
where email ilike ANY (ARRAY['[email protected]','[email protected]']);
Bitmap Heap Scan on y  (cost=126.07..154.50 rows=20 width=29) (actual time=60.696..60.818 rows=2 loops=1)
  Recheck Cond: (email ~~* ANY ('{[email protected],[email protected]}'::text[]))
  Rows Removed by Index Recheck: 13
  Heap Blocks: exact=11
  ->  Bitmap Index Scan on ix_y  (cost=0.00..126.07 rows=20 width=0) (actual time=60.661..60.661 rows=15 loops=1)
        Index Cond: (email ~~* ANY ('{[email protected],[email protected]}'::text[]))
Planning time: 0.952 ms
Execution time: 61.004 ms

Und für die Verwendung von citext:

explain (analyze)
select * 
from x 
where email = ANY (ARRAY['[email protected]','[email protected]']);
Index Scan using x_email_key on x  (cost=0.42..5.85 rows=2 width=29) (actual time=0.111..0.203 rows=2 loops=1)
  Index Cond: (email = ANY ('{[email protected],[email protected]}'::citext[]))
Planning time: 0.115 ms
Execution time: 0.254 ms

Beachten Sie, dass die Abfrage ILIKE tatsächlich etwas anderes ist als die Abfrage = Abfrage nach citext, da ILIKE Platzhalterzeichen berücksichtigen würde.


Bei einem nicht eindeutigen Index sieht es jedoch anders aus. Das folgende Setup basiert auf einer kürzlich gestellten Frage , die dasselbe fragt:

create table data
(
  group_id serial primary key,
  name text
);

create table data_ci
(
  group_id serial primary key,
  name citext
);

insert into data(name)
select 'data'||i.n
from generate_series(1,1000) as i(n), generate_series(1,1000) as i2(n);

insert into data_ci(group_id, name)
select group_id, name
from data;

create index ix_data_gin on data using gin (name public.gin_trgm_ops);
create index ix_data_ci on data_ci (name);

Wir haben also eine Million Zeilen in jeder Tabelle und 1000 verschiedene Werte für die Spalte name und für jeden einzelnen Wert 1000 Duplikate. Eine Abfrage, die nach 3 verschiedenen Werten sucht, gibt daher 3000 Zeilen zurück.

In diesem Fall ist der Trigrammindex wesentlich schneller als der BTree-Index:

explain (analyze)
select *
from data 
where name ilike any (array['Data1', 'data2', 'DATA3']);
Bitmap Heap Scan on data  (cost=88.25..1777.61 rows=1535 width=11) (actual time=2.906..11.064 rows=3000 loops=1)
  Recheck Cond: (name ~~* ANY ('{Data1,data2,DATA3}'::text[]))
  Heap Blocks: exact=17
  ->  Bitmap Index Scan on ix_data_gin  (cost=0.00..87.87 rows=1535 width=0) (actual time=2.869..2.869 rows=3000 loops=1)
        Index Cond: (name ~~* ANY ('{Data1,data2,DATA3}'::text[]))
Planning time: 2.174 ms
Execution time: 11.282 ms

Und der Btree-Index in der Citext-Spalte verwendet jetzt einen Seq-Scan

explain analyze
select *
from data_ci
where name = any (array['Data1', 'data2', 'DATA3']);
Seq Scan on data_ci  (cost=0.00..10156.00 rows=2904 width=11) (actual time=0.449..304.301 rows=1000 loops=1)
  Filter: ((name)::text = ANY ('{Data1,data2,DATA3}'::text[]))
  Rows Removed by Filter: 999000
Planning time: 0.152 ms
Execution time: 304.360 ms

Außerdem ist der GIN-Index kleiner als in der Spalte citext:

select pg_size_pretty(pg_total_relation_size('ix_data_gin')) as gin_index_size, 
       pg_size_pretty(pg_total_relation_size('ix_data_ci')) as citex_index_size
gin_index_size | citex_index_size
---------------+-----------------
11 MB          | 21 MB           

Das obige wurde mit Postgres 9.6.1 auf einem Windows-Laptop mit random_page_cost auf 1.5 gesetzt

11

Verwenden Sie den Datentyp "Text ohne Berücksichtigung der Groß-/Kleinschreibung". Verwenden Sie citext:

create table emails
(
user_id int references users(user_id)
email citext
);

insert into emails(user_id, email) values(1, '[email protected]');
insert into emails(user_id, email) values(2, '[email protected]');

select * from emails where email in ('[email protected]','[email protected]');

Falls Sie die citext.sql nicht in Ihrem contrib-Verzeichnis finden, kopieren Sie diese und fügen Sie sie in Ihr pgAdmin ein:

/* $PostgreSQL: pgsql/contrib/citext/citext.sql.in,v 1.3 2008/09/05 18:25:16 tgl Exp $ */

-- Adjust this setting to control where the objects get created.
SET search_path = public;

--
--  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 OR REPLACE FUNCTION citextin(cstring)
RETURNS citext
AS 'textin'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citextout(citext)
RETURNS cstring
AS 'textout'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citextrecv(internal)
RETURNS citext
AS 'textrecv'
LANGUAGE internal STABLE STRICT;

CREATE OR REPLACE FUNCTION citextsend(citext)
RETURNS bytea
AS 'textsend'
LANGUAGE internal STABLE STRICT;

--
--  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
);

--
-- Type casting functions for those situations where the I/O casts don't
-- automatically kick in.
--

CREATE OR REPLACE FUNCTION citext(bpchar)
RETURNS citext
AS 'rtrim1'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext(boolean)
RETURNS citext
AS 'booltext'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext(inet)
RETURNS citext
AS 'network_show'
LANGUAGE internal IMMUTABLE STRICT;

--
--  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 OR REPLACE FUNCTION citext_eq( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext_ne( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext_lt( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext_le( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext_gt( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext_ge( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;

--
-- 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 OR REPLACE FUNCTION citext_cmp(citext, citext)
RETURNS int4
AS '$libdir/citext'
LANGUAGE C STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION citext_hash(citext)
RETURNS int4
AS '$libdir/citext'
LANGUAGE C STRICT IMMUTABLE;

--
-- 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 OR REPLACE FUNCTION citext_smaller(citext, citext)
RETURNS citext
AS '$libdir/citext'
LANGUAGE 'C' IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext_larger(citext, citext)
RETURNS citext
AS '$libdir/citext'
LANGUAGE 'C' IMMUTABLE STRICT;

CREATE AGGREGATE min(citext)  (
    SFUNC = citext_smaller,
    STYPE = citext,
    SORTOP = <
);

CREATE AGGREGATE max(citext)  (
    SFUNC = citext_larger,
    STYPE = citext,
    SORTOP = >
);

--
-- CITEXT pattern matching.
--

CREATE OR REPLACE FUNCTION texticlike(citext, citext)
RETURNS bool AS 'texticlike'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION texticnlike(citext, citext)
RETURNS bool AS 'texticnlike'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION texticregexeq(citext, citext)
RETURNS bool AS 'texticregexeq'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION texticregexne(citext, citext)
RETURNS bool AS 'texticregexne'
LANGUAGE internal IMMUTABLE STRICT;

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 OR REPLACE FUNCTION texticlike(citext, text)
RETURNS bool AS 'texticlike'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION texticnlike(citext, text)
RETURNS bool AS 'texticnlike'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION texticregexeq(citext, text)
RETURNS bool AS 'texticregexeq'
LANGUAGE internal IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION texticregexne(citext, text)
RETURNS bool AS 'texticregexne'
LANGUAGE internal IMMUTABLE STRICT;

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 OR REPLACE FUNCTION regexp_matches( citext, citext ) RETURNS TEXT[] AS $$
    SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION regexp_matches( citext, citext, text ) RETURNS 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;

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;
11
Michael Buen

Sie können auch einen Index für niedriger (E-Mail) erstellen.

2
peufeu