Received: from Fallon.classyad.com (IDENT:root@nic-c61-156.mw.mediaone.net [24.131.61.156]) by hub.org (8.9.2/8.9.1) with ESMTP id VAA29024 for ; Sun, 7 Feb 1999 21:46:09 -0500 (EST) (envelope-from marc@fallon.classyad.com) Received: from localhost (IDENT:marc@localhost [127.0.0.1]) by Fallon.classyad.com (8.9.0/8.9.0) with SMTP id VAA20998; Sun, 7 Feb 1999 21:42:23 -0500 Date: Sun, 7 Feb 1999 21:42:23 -0500 (EST) From: Marc Howard Zuckman To: Sascha Schumann cc: Tom Lane , Bryan White , pgsql-sql@hub.org, pgsql-hackers@postgreSQL.org Subject: Re: [SQL] Functional Indexes In-Reply-To: <19990208012826.A10851@schell.de> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII On Mon, 8 Feb 1999, Sascha Schumann wrote: > On Sat, Feb 06, 1999 at 12:27:47PM -0500, Tom Lane wrote: > > "Bryan White" 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 > I don't think lower is defined for varchar arguments. consider redefining username as type text and using text_ops. This method worked on my system: stocks=> create table temptext (a text, b varchar(20)); CREATE stocks=> create index itemptext on temptext using btree(lower(a) text_ops) ; CREATE Your error reproduced: stocks=> create index i2temptext on temptext using btree(lower(b) text_ops) ; ERROR: BuildFuncTupleDesc: function 'lower(varchar)' does not exist Excerpt from function definitions( both return value and argument are text types): text |lower |text |lowercase Marc Zuckman marc@fallon.classyad.com _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ _ Visit The Home and Condo MarketPlace _ _ http://www.ClassyAd.com _ _ _ _ FREE basic property listings/advertisements and searches. _ _ _ _ Try our premium, yet inexpensive services for a real _ _ selling or buying edge! _ _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_