Which databases can create function-based indexes?

Question

I know, that this type of indexes exist in Oracle. Old versions on MySQL can not create function-based indexes (thanks, google). And new versions? How about PostgreSQL, SQL Server etc?

Answer 1

PostgreSQL can create indexes on expressions including functions: Indexes on Expressions

Answer 2

I don't know inner details of oracle's, but postgres can create index on expression, which can be a function, from :

An index field can be an expression computed from the values of one or more columns of the table row. This feature can be used to obtain fast access to data based on some transformation of the basic data. For example, an index computed on upper(col) would allow the clause WHERE upper(col) = 'JIM' to use an index.

EDIT: MySQL seems to still be forging this, see virtual columns for details. Also some discussions here. Don't seem very active.

DB2 does it.

MS SQL can not do it, but using computed columns you can have similar effects; see discussion.

Answer 3

You could emulate function based indexes if your database supports insert and update triggers.

Add a column that will contain the function values and add index for that column. Then have your triggers to update column containing function values. Your queries have to change, replace function(params) with function_col.