Skip site navigation (1) Skip section navigation (2)

Re: Multifunction Indexes

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: David Wheeler <david(at)kineticode(dot)com>
Cc: <sfpug(at)postgresql(dot)org>
Subject: Re: Multifunction Indexes
Date: 2003-03-14 23:56:53
Message-ID: 20030314155128.M86697-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: sfpug
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?


In response to

Responses

sfpug by date

Next:From: David WheelerDate: 2003-03-15 00:04:39
Subject: Re: Multifunction Indexes
Previous:From: David WheelerDate: 2003-03-14 23:46:58
Subject: Multifunction Indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group