Re: [SQL] Functional Indexes

On Sat, Feb 06, 1999 at 12:27:47PM -0500, Tom Lane wrote:
> "Bryan White" <bryan@arcamax.com> writes:
> > The documentation for CREATE INDEX implies that functions are allowed in
> > index definitions but when I execute:
> > create unique index idxtest on customer (lower(email));
> > the result is:
> > ERROR:  DefineIndex: (null) class not found
> > Should this work?  Do I have the syntax wrong?
>
> I tried this wih 6.4.2 and found that it was only accepted if I
> explicitly identified which index operator class to use:
>
> play=> create table customer (email text);
> CREATE
> play=> create unique index idxtest on customer (lower(email));
> ERROR:  DefineIndex:  class not found
> play=> create unique index idxtest on customer (lower(email) text_ops);
> CREATE
> play=>
>
> That'll do as a workaround for Bryan, but isn't this a bug?  Surely
> the system ought to know what type the result of lower() is...
>
>             regards, tom lane

I still have a problem with that ... edited typescript follows

funweb=> \d userdat
Table    = userdat
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| username                         | varchar() not null               |    30 |
...
+----------------------------------+----------------------------------+-------+
Index:    userdat_pkey
funweb=> create unique index userdat_idx2 on userdat (lower(username)
varchar_ops);
ERROR:  BuildFuncTupleDesc: function 'lower(varchar)' does not exist

This error message looks very bogus to me.

--

          Regards,

                            Sascha Schumann |
                                 Consultant | finger sas@schell.de
                                            | for PGP public key