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.
