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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Thursday, October 12, 2000 12:49 AM
> To: Jeff Eckermann
> Cc: 'pgsql-general(at)postgresql(dot)org'
> Subject: Re: [GENERAL] 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