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/
