Re: expression index not used within function

[LPlateAndy <andy@xxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general