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;
