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

Multifunction Indexes

From: David Wheeler <david(at)kineticode(dot)com>
To: sfpug(at)postgresql(dot)org
Subject: Multifunction Indexes
Date: 2003-03-14 23:46:58
Message-ID: 3EE121C2-5677-11D7-8FDF-0003931A964A@kineticode.com (view raw or flat)
Thread:
Lists: sfpug
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));

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));

TIA,

David

-- 
David Wheeler                                     AIM: dwTheory
david(at)kineticode(dot)com                              ICQ: 15726394
                                                Yahoo!: dew7e
                                                Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]


Responses

sfpug by date

Next:From: Stephan SzaboDate: 2003-03-14 23:56:53
Subject: Re: Multifunction Indexes
Previous:From: David WheelerDate: 2003-03-14 23:42:22
Subject: Re: SFPUG Meetings

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