Here's a stab at formalizing this request a bit. First we start out with some definitions:
linearDatatype - Any datatype on which you can build a Btree index
stableOperator - A side-effect free SQL operator which always returns the same value when given the same operands. This includes the arithmetic, string, and boolean operators: +, -, *, /, <, >, =, CAST, ||.
stableFunction - A side-effect-free function which always returns the same value when given the same parameters. Being side-effect-free means that a stableFunction may not alter the database environment. For instance, it may not issue queries or alter the connection in any way. A stableFunction should not perform any i/o. This includes network and disk i/o. A stableFunction is supposed to be small and self-contained. It's what the literature calls a "pure lambda function".
indexableExpression - An expression, written in sql which satisfies the following conditions: 1) It may appear in the WHERE clause of a SELECT statement. 2) It contains no subqueries. 2) Its resolved datatype is a linearDatatype. 3) It is composed entirely of stableOperators, stableFunctions, constants, and columns from a single row from a single base table.
indexedExpression - An indexableExpression on which an index has been built.
This feature then lets users declare index columns to be indexableExpressions built up from columns in the base row. The feature also provides language support for marking indexedExpressions in queries as sargs (search arguments), for selecting the optimal join strategy given these sargs, and then evaluating the resultant query plan.
The CREATE INDEX syntax would change as follows:
CREATE [UNIQUE] INDEX index-Name
ON table-Name ( indexableExpression [ ASC | DESC ]
[ , indexableExpression [ ASC | DESC ]] * )
Here are some sketches for how we might implement this feature. Hopefully, the Store experts will respond with better suggestions:
1) Invisible Columns. In this approach, declaring an expression as an
indexed column would cause the Language to create an invisible
column in the table. At INSERT and UPDATE time, the Language would
compute the value of this column as necessary. We would need some
Store support for dropping these invisible columns when the
corresponding index is dropped.
2) Stored Expressions. In this approach, the stored index definition
would include a compiled version of the expression
which the Store would run to calculate the index column value. This
approach probably creates some complexity at recovery and upgrade
time. It also creates opportunities for Store corruption
if users include mischievous functions in their
indexableExpressions. Again, this would be particularly problematic
at recovery time.