IIRC, index functions can take multiple arguments, it's just that
they cannot take constants. I think the values must all be columns
of the tables. So for example, this should work (seems to at
least create on 7.1beta3):
create table aaa (a int, b int, c text);
create index blah on aaa(substr(c,a,b));
On Wed, 31 Jan 2001 pgsql-bugs@postgresql.org wrote:
> Scott Marlowe (smarlowe@ihs.com) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> cannot index functions with multiple arguments
>
> Long Description
> Postgresql version: 7.1beta2
> OS: RedHat Linux 7.0
>
> Postgresql allows indexing of functions applied to columns, like so:
>
> create index yada on table (lower(column));
>
> but this fails on functions like substr that have multiple arguments.
>
> create index yada on table (substr(column,1,4));
>
> fails like so:
>
> ERROR: parser: parse error at or near "1"
>
> Sample Code
> Exact code as I typed it in, with response:
>
> smarlowe=# create index exten_extension_idx on exten (substr(extension,1,4));
> ERROR: parser: parse error at or near "1"
>
> I hope this isn't a known bug and I'm reporting it again.
>
> No file was uploaded with this report
>