Re: Multifunction Indexes

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