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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org