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

On Mon, Feb 07, 2005 at 03:16:51PM -0600, Larry Rosenman wrote:
> SELECT domain,message,'1' as truth FROM blacklist
> WHERE somedomain ~* '(?:.+\.|)' || domain || '\$')
>
> The somedomain is actually a constant passed in from Exim (it's the sender's
> righthand
> Side of an E-Mail address).
>
> I'm looking to see if the domain name is in my blacklist.
>
> I may just be SOL, but I figured I'd ask.
>
> The blacklist table is:
> exim=# \d blacklist
>                        Table "public.blacklist"
>    Column    |            Type             |        Modifiers
> -------------+-----------------------------+--------------------------
>  insert_when | timestamp(0) with time zone | default now()
>  insert_who  | text                        | default "current_user"()
>  domain      | text                        |
>  message     | text                        |
> Indexes:
>     "blacklist_dom_idx" btree ("domain")
>
> exim=#
>
> And contains records like:
>
> exim=# select * from blacklist limit 1;
>       insert_when       | insert_who |  domain  |             message
> ------------------------+------------+----------+---------------------------
> ------
>  2003-12-22 21:02:49-06 | ler        | 008\.net | 127.0.0.1 MX, SPAMMER
> (008.net)

A functional btree index on reverse(domain) might get you what you're
looking for.

<digs in the Abacus source code...>

  CREATE OR REPLACE 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;

Then do

CREATE INDEX foo_idx ON blacklist(reverse(domain));

SELECT * FROM blacklist WHERE reverse(domain) LIKE reverse(bar) || '%';

Cheers,
  Steve