RE: Index on substring?

From: Jeff Eckermann <jeckermann(at)verio(dot)net>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Subject: RE: Index on substring?
Date: 2000-10-12 14:18:52
Message-ID: 08CD1781F85AD4118E0800A0C9B8580B09472E@NEZU
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adam Lang 2000-10-12 14:21:11 Re: Re: [HACKERS] My new job
Previous Message Lamar Owen 2000-10-12 14:17:07 Re: Re: [HACKERS] My new job