X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B34A332A219 for ; Thu, 21 Oct 2004 15:23:29 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33848-04 for ; Thu, 21 Oct 2004 14:23:23 +0000 (GMT) Received: from ll.mit.edu (LLMAIL.LL.MIT.EDU [129.55.12.40]) by svr1.postgresql.org (Postfix) with ESMTP id 2E72732A263 for ; Thu, 21 Oct 2004 15:23:20 +0100 (BST) Received: (from smtp@localhost) by ll.mit.edu (8.12.10/8.8.8) id i9LEMxRs000661; Thu, 21 Oct 2004 10:22:59 -0400 (EDT) Received: from sty.llan.ll.mit.edu( ), claiming to be "sty.llan" via SMTP by llpost, id smtpdAAA8mayq6; Thu Oct 21 10:22:37 2004 Date: Thu, 21 Oct 2004 10:22:37 -0400 From: george young To: "Ray" Cc: pgsql-performance@postgresql.org Subject: Re: create index with substr function Message-Id: <20041021102237.5ae1d14e.gry@ll.mit.edu> In-Reply-To: <078b01c4b71f$4936e4e0$0f6010ac@raysiu> References: <069201c4b715$34851670$0f6010ac@raysiu> <37d451f704102020346fdbb855@mail.gmail.com> <078b01c4b71f$4936e4e0$0f6010ac@raysiu> Reply-To: gry@ll.mit.edu Organization: MIT Lincoln Laboratory X-Mailer: Sylpheed version 0.9.10 (GTK+ 1.2.10; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/352 X-Sequence-Number: 8828 As previously suggested by Stephan Szabo, you need to create a helper function, e.g.: create or replace function after9(text)returns text language plpgsql immutable as ' begin return substr($1, 10); end; '; You may need the "immutable" specification is to allow the function's use in an index. Then use this function in the index creation: CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (after9(doc_urn)); I think that should do it. -- George > On Thu, 21 Oct 2004 11:37:26 +0800 "Ray" threw this fish to the penguins: > sorry it doesn't works, as my postgres is 7.3 not 7.4. any other alternative > solution for version after 7.4?? > > Thank > Ray : ) > > ----- Original Message ----- > From: "Rosser Schwarz" > To: "Ray" > Cc: > Sent: Thursday, October 21, 2004 11:34 AM > Subject: Re: [PERFORM] create index with substr function > > > > while you weren't looking, Ray wrote: > > > > > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree > (SUBSTR(doc_urn,10)); > > > > CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree > ((SUBSTR(doc_urn,10))); > > > > You need an additional set of parens around the SUBSTR() call. > > > > /rls > > > > -- > > :wq > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL)