Re: Multifunction Indexes

On Fri, 14 Mar 2003, David Wheeler wrote:

> Hi all,
>
> Anyone know if there are plans to add multifunction or function +
> column indexes in the near future? I'd like to be able to do this:
>
> CREATE UNIQUE INDEX on workflow(LOWER(name), site_id);
>
> And this:
>
> CREATE UNIQUE INDEX on workflow(LOWER(this), TEXT(that));

Not that I've heard in the short term. The data structures are
insufficient, so it might take some work.

> And a related question. Until the above is implemented, I have VARCHAR
> and NUMERIC(10, 0) columns I want to create a UNIQUE index on, with the
> VARCHAR column LOWERed. To do this, I have to create a single function
> that takes one of each of these types. Is this an appropriate function?
>
> CREATE FUNCTION lower(TEXT, NUMERIC(10, 0))
> RETURNS TEXT AS 'SELECT LOWER($1) || TEXT($2)' LANGUAGE 'sql'
> WITH (iscachable);
>
> CREATE UNIQUE INDEX on workflow(lower(name, site_id));

You need to be careful that you can't get duplicates from different value
pairs. In the above, I think $1 values ending in numerics could cause you
problems. Maybe using something like to_char($2, '09999999999') would
work better?