RE: Index on substring?

Tom,
Thanks very much for your full and clear answer.
It's hard to imagine a general use for this facility, anyway.
For me this is a one-off exercise, albeit a big one.
Regards

> -----Original Message-----
> From:    Tom Lane [SMTP:tgl@sss.pgh.pa.us]
> Sent:    Thursday, October 12, 2000 12:49 AM
> To:    Jeff Eckermann
> Cc:    'pgsql-general@postgresql.org'
> Subject:    Re: [GENERAL] Index on substring?
>
> 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