Re: citext function overloads for text parameters

2018-05-06 8:26 GMT+02:00 Shay Rojansky <roji(at)roji(dot)org>:

> Hi hackers.
>
> The following works well of course:
>
> test=# select strpos('Aa'::citext, 'a');
> strpos
> --------
> 1
>
> However, if I pass a typed text parameter for the substring, I get
> case-sensitive behavior instead:
>
> test=# select strpos('Aa'::citext, 'a'::text);
> strpos
> --------
> 2
>
> This seems like surprising behavior - my expectation was that the first
> parameter being citext would be enough to trigger case-insensitive
> behavior. The same may be happening with other string functions (e.g.
> regexp_matches). This is causing some difficulties in a real scenario where
> SQL and parameters are getting generated by an O/RM, and changing them
> isn't trivial.
>
> Do the above seem like problematic behavior like it does to me, or is it
> the expected behavior?
>

This is expected - it is side effect of PostgreSQL implementation of
function overloading and type conversions

after installation citext, you will have more instances of function strpos

strpos(citext, citext)
strpos(text, text)

the call strpos('aa'::citext, 'a') is effective strpos('aa'::citext,
'a'::unknown) and that strpos(citext, citext) can be used in this case.

strpos('aa'::citext, 'a'::text) is ambiguous (both functions can be used
with necessary conversion - cast citext<->text is available), and usually
it fails with related error message - but there is a exception - the text
type is PREFERRED - what means, so strpost(text, text) is selected.

PostgreSQL type system is very generic and works almost well, but sometimes
there can be unwanted effects when some functions are overloaded. In this
case is better to implement own instance of unique function and use only it.

some like

create or replace function strpos_ci(text, text) returns int as $$ select
strpos($1::citext, $2::citext) $$ language sql;
create or replace function strpos_ci(citext, citext) returns int as $$
select strpos($1, $1) $$ language sql;

Regards

Pavel

>
> Shay
>