Thanks Tom. I'll try the "EXECUTE" method as well but my dev environment is 9.2 and the planner doesn't seem to be including the index so following are the fairly basic table/index/function details. Thanks, Andy:
================================ TABLE (circa 300,000 rows): ================================ CREATE TABLE postcode ( gid serial NOT NULL, pcode text, e integer, n integer, geometry geometry(Geometry,27700), CONSTRAINT postcode_pkey PRIMARY KEY (gid) ) WITH ( OIDS=FALSE ); ALTER TABLE postcode OWNER TO postgres; ================================ TABLE INDEX: ================================ CREATE INDEX idx_postcode_lc_pcode ON postcode USING btree (replace(lower(pcode), ' '::text, ''::text) COLLATE pg_catalog."default" text_pattern_ops); ================================ SELECT FUNCTION: ================================ CREATE OR REPLACE FUNCTION _search_pcode(IN text) RETURNS TABLE(searchmatch text, geometry geometry) AS $BODY$ SELECT pcode searchmatch, geometry FROM postcode WHERE (replace(lower(pcode), ' '::text, ''::text)) LIKE (replace((lower($1)::text),' '::text,''::text)||'%'::text) LIMIT 20; $BODY$ LANGUAGE sql IMMUTABLE SECURITY DEFINER COST 100 ROWS 1000; ALTER FUNCTION _search_pcode(text) OWNER TO postgres; GRANT EXECUTE ON FUNCTION _search_pcode(text) TO public; GRANT EXECUTE ON FUNCTION _search_pcode(text) TO postgres; -- View this message in context: http://postgresql.1045698.n5.nabble.com/expression-index-not-used-within-function-tp5778236p5778321.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
