For a functional index, an index is defined
on the result of a function applied to one or more columns of a
single table. Functional indexes can be used to obtain fast access
to data based on the result of function calls.
For example, a common way to do case-insensitive comparisons is to
use the lower function:
SELECT * FROM test1 WHERE lower(col1) = 'value';
This query can use an index, if one has been
defined on the result of the lower(column)
operation:
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
The function in the index definition can take more than one
argument, but they must be table columns, not constants.
Functional indexes are always single-column (namely, the function
result) even if the function uses more than one input field; there
cannot be multicolumn indexes that contain function calls.
Tip: The restrictions mentioned in the previous paragraph can easily be
worked around by defining a custom function to use in the index
definition that computes any desired result internally.
