Re: Index on substring?

Jeff Eckermann <jeckermann(at)verio(dot)net> writes:
> extracts=# create index c_namesum_i on customers (substr(bill_company,1,5));
> ERROR: parser: parse error at or near "1"

The functional-index syntax only allows a function name applied to
simple column names.

You can work around this by defining a function that handles any
additional computation needed, eg,

create index c_namesum_i on customers (mysubstr15(bill_company));

where mysubstr15(foo) returns substr(foo,1,5). In current releases
the intermediate function has to be in C or a PL language. 7.1 will
allow a SQL-language function too (although frankly I'd recommend
against using a SQL function for indexing, on performance grounds).

There's been some talk of generalizing the functional-index support
into arbitrary-expression-index support, but it doesn't seem to be
real high on anyone's priority list.

regards, tom lane