How to create SQLite index for different datatype

When I create index on certain column, index is datatype-aware, however I can store data of any type in any column, but it makes the index somewhat useless if I want to compare column content using different comparison rules, e.g. compare numbers as numbers, not strings.

Example:

sqlite> create table foo (key varchar, value varchar);
sqlite> create index foo_ndx on foo (key, value);
sqlite> insert into foo values ('bar', 10);

sqlite> select * from foo where key = 'bar' and value > 9.0;
sqlite> explain query plan select * from foo where key = 'bar' and value > 9.0;
0|0|0|SEARCH TABLE foo USING COVERING INDEX foo_ndx (key=? AND value>?) (~2 rows)

In this case 9.0 gets implicitly casted as varchar and two varchars get compared, index is used fully, but the record cannot be found.

sqlite> select * from foo where key = 'bar' and CAST(value AS REAL) > 9.0;
bar|10
sqlite> explain query plan select * from foo where key = 'bar'
   ...> and CAST(value AS REAL) > 9.0;
0|0|0|SEARCH TABLE foo USING COVERING INDEX foo_ndx (key=?) (~5 rows)

In this case I cast value to real explicitly and two reals get compared, sqlite finds the record, but the index used only partially.

This is understandable behaviour, however can I somehow create index on foo table that will treat column value as REAL? I've tried using CAST in CREATE INDEX but got syntax error, I can create view on foo table with CAST, but index cannot be created on view.