Re: Creating an index-type for LIKE '%value%'

On Mon, 7 Feb 2005, Steve Atkins wrote:
> A functional btree index on reverse(domain) might get you what you're
> looking for.

[snip]

I wound up doing the following:

--
-- Name: reverse(text); Type: FUNCTION; Schema: public; Owner: ler
--

CREATE FUNCTION reverse(text) RETURNS text
     AS $_$
DECLARE
original alias for $1;
      reverse_str text;
      i int4;
BEGIN
    reverse_str := '';
    FOR i IN REVERSE LENGTH(original)..1 LOOP
      reverse_str := reverse_str || substr(original,i,1);
    END LOOP;
RETURN reverse_str;
END;$_$
     LANGUAGE plpgsql IMMUTABLE;


ALTER FUNCTION public.reverse(text) OWNER TO ler;

--
-- Name: update_new_domain2(); Type: FUNCTION; Schema: public; Owner: ler
--

CREATE FUNCTION update_new_domain2() RETURNS "trigger"
     AS $$
BEGIN
IF TG_OP = 'DELETE'
THEN RETURN OLD;
END IF;
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE'
THEN NEW.new_domain2 :=  (reverse(lower('%' || NEW.domain)) );
      RETURN NEW;
END IF;
END;
$$
     LANGUAGE plpgsql IMMUTABLE;


ALTER FUNCTION public.update_new_domain2() OWNER TO ler;

--
-- Name: blacklist; Type: TABLE; Schema: public; Owner: ler; Tablespace:
--

CREATE TABLE blacklist (
     insert_when timestamp(0) with time zone DEFAULT now(),
     insert_who text DEFAULT "current_user"(),
     message text NOT NULL,
     "domain" text NOT NULL,
     new_domain2 text NOT NULL
);
ALTER TABLE ONLY blacklist ALTER COLUMN "domain" SET STATISTICS 100;
ALTER TABLE ONLY blacklist ALTER COLUMN new_domain2 SET STATISTICS 100;


ALTER TABLE public.blacklist OWNER TO ler;
--
-- Name: blk_new_idx3; Type: INDEX; Schema: public; Owner: ler; Tablespace:
--

CREATE INDEX blk_new_idx3 ON blacklist USING btree (new_domain2);

ALTER TABLE blacklist CLUSTER ON blk_new_idx3;


ALTER INDEX public.blk_new_idx3 OWNER TO ler;

--
-- Name: blacklist_domain; Type: TRIGGER; Schema: public; Owner: ler
--

CREATE TRIGGER blacklist_domain
     BEFORE INSERT OR DELETE OR UPDATE ON blacklist
     FOR EACH ROW
     EXECUTE PROCEDURE update_new_domain2();

It doesn't yet use the index with the 254 domains I have in my fecal roster, but
it's also about 5x as fast as the other REGEX lookup.

Thanks for the ideas!

LER

--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749