I wrote: > "Dmitri Bichko" <[EMAIL PROTECTED]> writes: >> So, is there any way to make these operators use an index defined as >> above?
> If you've set things up so that the operators are defined by inline-able > SQL functions, I'd sort of expect it to fall out for free ... Here's a quick proof-of-concept: regression=# create function iequal(text,text) returns bool as regression-# 'select upper($1) = upper($2)' language sql strict immutable; CREATE FUNCTION regression=# create operator *= (procedure = iequal, leftarg = text, regression(# rightarg = text , commutator = *= ); CREATE OPERATOR regression=# explain select * from text_tbl where f1 *= 'foo'; QUERY PLAN --------------------------------------------------------- Seq Scan on text_tbl (cost=0.00..1.03 rows=1 width=32) Filter: (upper(f1) = 'FOO'::text) (2 rows) regression=# create index fooi on text_tbl(upper(f1)); CREATE INDEX regression=# set enable_seqscan TO 0; -- because my test table is tiny SET regression=# explain select * from text_tbl where f1 *= 'foo'; QUERY PLAN ---------------------------------------------------------------------- Index Scan using fooi on text_tbl (cost=0.00..4.68 rows=1 width=32) Index Cond: (upper(f1) = 'FOO'::text) (2 rows) This is with CVS tip, but I'm pretty sure it works as far back as 7.4. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq