"Patrick FICHE" <pfiche@prologue-software.fr> writes:
> I would like to use some indexes with functions like substr :
> CREATE INDEX IND1 ON T1 ( substr( col1, 1, 5 ) )...
Right now you can't do that: the functional-index support only
handles cases like
function ( columnname [ , columnname [ , ... ]] )
No constants, no expressions, just one function invoked on one or
more unadorned column names.
Of course, you can get around that pretty easily by writing a
PL function that does exactly the computation you need. But it's
still an annoying restriction. (I think we have someone looking
into relaxing the restriction, so that you can build a functional
index on any expression that uses one table's columns.)
What you seem to be asking, though, is whether the system is able
to do anything with a functional index on expression A for a
query involving not-very-closely-related expression B. The answer
is no...
regards, tom lane