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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > To: "Ray" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > 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 [EMAIL PROTECTED]) > -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
