Jeff Eckermann <jeckermann@verio.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
