Postgres Pro
Downloads
Home   >   mailing lists

Re: citext function overloads for text parameters - Mailing list pgsql-hackers

From Sergey Mirvoda
Subject Re: citext function overloads for text parameters
Date
Msg-id CALkWArh3QNyGednTtUTwxaHqTSkAEZzxS8+RsFVn6eOQ1mYuGw@mail.gmail.com
Whole thread Raw
In response to Re: citext function overloads for text parameters  (Shay Rojansky <roji@roji.org>)
List pgsql-hackers
Tree view 
Hello hanckers,

We use this simple function to workaround citext=text behavior.


create extension citext;

CREATE FUNCTION citext_eq( citext, text )
RETURNS bool
AS 'citext'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR = (
    LEFTARG    = CITEXT,
    RIGHTARG   = TEXT,
    COMMUTATOR = =,
    NEGATOR    = <>,
    PROCEDURE  = citext_eq,
    RESTRICT   = eqsel,
    JOIN       = eqjoinsel,
    HASHES,
    MERGES
);

select 'xexe'::text = 'Xexe'::citext
select 'xexe' = 'Xexe'::citext
select 'xexe'::citext = 'Xexe'
select 'xexe'::citext = 'Xexe'::text
select 'xexe'::citext = 1234::text


CREATE or replace FUNCTION ttt(t text)
RETURNS bool
AS
$$select $1 = 'Ttt'::citext  $$
LANGUAGE sql;

select ttt('ttt')


But in general, it is wrong to compare values with different types.
We used this and other strange cases like TO_CHAR for type text  for our own BI system with user defined calculations .

 

On Mon, May 7, 2018 at 12:09 PM, Shay Rojansky <roji@roji.org> wrote:

Thanks for the input. It's worth noting that the equality operator currently works in the same way: citext = text comparison is (surprisingly for me) case-sensitive.

My expectation was that since citext is supposed to be a case-insensitive *type*, all comparison operations involving it should be case-insensitive;

Comparison requires both things to be the same type.  The rules for implicitly converting one type to another prefer the core type text over the extension type citext.

IOW, there is no such operator =(citext,text) and thus "citext = text comparison" is technically invalid.

At this point we're sorta stuck with our choice, and while individual databases can implement their own functions and operators there is value in doing things the way the system provides to minimize future confusion and bugs.

OK, thanks for everyone's input. 




--
--Regards, Sergey Mirvoda

pgsql-hackers by date:

Previous
From: Konstantin Knizhnik
Date:
Subject: Re: Built-in connection pooling
Next
From: Ashutosh Bapat
Date:
Subject: Re: MAP syntax for arrays