From: | Sergey Mirvoda <sergey(at)mirvoda(dot)com> |
---|---|
To: | Shay Rojansky <roji(at)roji(dot)org> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: citext function overloads for text parameters |
Date: | 2018-05-07 13:10:03 |
Message-ID: | CALkWArh3QNyGednTtUTwxaHqTSkAEZzxS8+RsFVn6eOQ1mYuGw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)roji(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Ashutosh Bapat | 2018-05-07 13:19:17 | Re: MAP syntax for arrays |
Previous Message | Konstantin Knizhnik | 2018-05-07 11:20:30 | Re: Built-in connection pooling |