Re: CREATE INDEX function limitation

brichard(at)cafod(dot)org(dot)uk (Bruce Richardson) writes:
> In a CREATE INDEX statement, functions don't do type conversion and
> can't be nested, meaning that the cast convertion function won't work.
> So,
> CREATE INDEX testidx on testtable (upper(CAST colname AS TEXT));
> fails. Is this a feature I should just work around?

There's an oversight in the CREATE INDEX code in 7.0.* and before,
which is that it rejects functions that are actually binary-compatible
with the column datatype. This is fixed in 7.1, meaning that you can
apply upper() to char(n) and varchar(n) columns not only text columns.
Dunno if that's the only case you care about. In the general case you
still can't ask for an arbitrary casting, because that would mean
application of a conversion function, and we don't handle anything
beyond one function call as the definition of a functional index.

The standard workaround is to define your own function that encapsulates
whatever computation you need to perform. This is kind of a pain in the
neck though, so there's been talk of extending "functional indexes" into
"expressional indexes" that would accept any scalar expression as their
definition. Maybe someday ...

regards, tom lane