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

Re: Multifunction Indexes

From: David Wheeler <david(at)kineticode(dot)com>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: <elein(at)varlena(dot)com>, <sfpug(at)postgresql(dot)org>
Subject: Re: Multifunction Indexes
Date: 2003-03-15 04:30:42
Message-ID: E1EC4332-569E-11D7-8FDF-0003931A964A@kineticode.com (view raw or flat)
Thread:
Lists: sfpug
On Friday, March 14, 2003, at 06:40  PM, Stephan Szabo wrote:

> I think you still need to do it as a unique index.  I don't think the
> UNIQUE() constraint syntax will take it (which is just a unique index) 
> and
> doing your own unique is painful at best.

Yep, I just tried and decided it was a big PITA -- the index is much 
easier. So this is what I've gone with:

   CREATE   FUNCTION lower_text_num(TEXT, NUMERIC(10, 0))
   RETURNS  TEXT AS 'SELECT LOWER($1) || to_char($2, ''|FM9999999999'')'
   LANGUAGE 'sql'IMMUTABLE;

   CREATE UNIQUE INDEX udx_alert_type__name__usr__id
     ON alert_type(lower_text_num(name, usr__id));

Thanks for the help, it's much appreciated.

Regards,

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]


In response to

sfpug by date

Next:From: Josh BerkusDate: 2003-03-15 22:43:36
Subject: Speak Now or be Unsubscribed!
Previous:From: David WheelerDate: 2003-03-15 04:29:01
Subject: Re: Multifunction Indexes

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