Re: Multifunction Indexes

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]