Re: Multifunction Indexes

On Friday, March 14, 2003, at 03:56 PM, Stephan Szabo wrote:

>> 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.

Pity. I think that's a fairly important feature that many might find
significant.

>> 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?

Oh, yeah, good spot! So you're suggesting this:

CREATE FUNCTION lower(TEXT, NUMERIC(10, 0))
RETURNS TEXT AS 'SELECT LOWER($1) || to_char($2, ''09999999999'')'
LANGUAGE 'sql'
WITH (iscachable);

Would that be more efficient/precise than, say, this?:

CREATE FUNCTION lower(TEXT, NUMERIC(10, 0))
RETURNS TEXT AS 'SELECT LOWER($1) || '|' || TEXT($2)'
LANGUAGE 'sql'
WITH (iscachable);

Thanks,

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]