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: <sfpug(at)postgresql(dot)org>
Subject: Re: Multifunction Indexes
Date: 2003-03-15 00:04:39
Message-ID: B7080E78-5679-11D7-8FDF-0003931A964A@kineticode.com (view raw or flat)
Thread:
Lists: sfpug
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]


In response to

Responses

sfpug by date

Next:From: eleinDate: 2003-03-15 00:25:52
Subject: Re: Multifunction Indexes
Previous:From: Stephan SzaboDate: 2003-03-14 23:56:53
Subject: Re: Multifunction Indexes

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