Re: Arrays and LIKE

On Sat, Aug 08, 2009 at 05:04:29PM +0930, David wrote:
> Done a bit of hunting and can't seem to find an answer as to if this
> sort of thing is possible:
>
> SELECT * FROM mail WHERE recipients ILIKE 'david%';
>
> Where recipients is a VARCHAR(128)[]

It's a bit of a fiddle:

CREATE FUNCTION flipilike(text,text)
RETURNS boolean IMMUTABLE LANGUAGE SQL
AS $$ SELECT $2 ILIKE $1; $$;
CREATE OPERATOR ~~~ (
leftarg = text,
rightarg = text,
procedure = flipilike
);

PG now understands:

SELECT 'x%' ~~~ 'fred';

To be the same as:

SELECT 'fred' ILIKE 'x%';

So you can solve your original problem as:

SELECT * FROM mail WHERE 'david%' ~~~ ANY(recipients);

> The above doesn't work but thats the sort of thing I want to do...
> If this is possible and can use an index as well that would be wonderful...

No idea about that, but I'd look to a GIN index to start with. I think
you really want to stop using arrays and do it "properly" with a
relation:

CREATE TABLE mailaddrs (
msgid TEXT REFERENCES mail,
ord INTEGER,
PRIMARY KEY (msgid, ord),
type TEXT CHECK (type IN ('to','from','cc','bcc')),
address TEXT
);
CREATE INDEX mailaddrs_address_idx ON mailaddrs (address);

then you can do:

SELECT DISTINCT msgid
FROM mailaddrs
WHERE address ILIKE 'david%';

and it should do the right thing. Not sure if you have this
flexibility though.

--
Sam http://samason.me.uk/