Re: [PERFORM] create index with substr function

[Joshua D. Drake]

Tom Lane wrote: "Ray" <[EMAIL PROTECTED]> writes: CREATE INDEX idx_doc_substr_doc_urn ON doc USING btree (SUBSTR(doc_urn,10)); ERROR: parser: parse error at or near "10" at character 68 This will work in 7.4, but not older releases. Can't you just use a SQL function that calls the substr function? I have done that with date functions before like: CREATE OR REPLACE FUNCTION get_month(text) returns double precision AS ' SELECT date_part('month',$1); ' LANGUAGE 'SQL' IMMUTABLE; CREATE INDEX get_month_idx on foo(get_month(date_field)); Or in this case: CREATE OR REPLACE FUNCTION sub_text(text) returns text AS '       SELECT SUBSTR($1,10) from foo; ' LANGUAGE 'SQL' IMMUTABLE; CREATE INDEX sub_text_idx ON foo(sub_text(doc_urn)); This works on 7.3.6??? Sincerely, Joshua D. Drake regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL