Received: from guerilla.foo.bar (qmailr@hennen32s.iserlohn.netsurf.de [194.195.194.226]) by hub.org (8.9.2/8.9.1) with SMTP id TAA13473 for ; Sun, 7 Feb 1999 19:27:32 -0500 (EST) (envelope-from sas@schell.de) Received: (qmail 10938 invoked by uid 1000); 8 Feb 1999 00:28:26 -0000 Date: Mon, 8 Feb 1999 01:28:26 +0100 From: Sascha Schumann To: Tom Lane Cc: Bryan White , pgsql-sql@hub.org, pgsql-hackers@postgreSQL.org Subject: Re: [SQL] Functional Indexes Message-ID: <19990208012826.A10851@schell.de> References: <199902060400.XAA31039@hub.org> <20086.918322067@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.95.1i In-Reply-To: <20086.918322067@sss.pgh.pa.us>; from Tom Lane on Sat, Feb 06, 1999 at 12:27:47PM -0500 X-Operating-System: Linux guerilla 2.2.1 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