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

Re: Multifunction Indexes

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: David Wheeler <david(at)kineticode(dot)com>
Cc: <sfpug(at)postgresql(dot)org>
Subject: Re: Multifunction Indexes
Date: 2003-03-15 00:43:58
Message-ID: 20030314163014.N87074-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: sfpug
On Fri, 14 Mar 2003, David Wheeler wrote:

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

Definately not more efficient but probably more correct.

sszabo=# create table test(a text, b numeric(10,0));
CREATE TABLE
sszabo=# insert into test values ('A', 123);
INSERT 17065 1
sszabo=# insert into test values ('A1', 23);
INSERT 17066 1
sszabo=# select lower(a) || TEXT(b) from test;
 ?column?
----------
 a123
 a123
(2 rows)

sszabo=# select lower(a) || to_char(b, '09999999999') from test;
    ?column?
----------------
 a 00000000123
 a1 00000000023
(2 rows)

(I guess you might really want FM0... but...)



In response to

Responses

sfpug by date

Next:From: eleinDate: 2003-03-15 01:20:05
Subject: Re: Multifunction Indexes
Previous:From: David WheelerDate: 2003-03-15 00:39:53
Subject: Re: Multifunction Indexes

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