Thanks all normally I would have gone with a linked table but since support for arrays has improved in pg lately I thought I would give them a go again but I guess they are still not ready for what I want. I did think of another solution overnight though that still uses arrays but also a subtable. where I add address to a another table with a id sequence and then store the seqid in the array then I could do the like on the subtable returning ids and use the array of ids it returns to compare to the recipient ids in the array (this should use a GIN index as overlapping arrays is listed on the page http://www.postgresql.org/docs/8.3/interactive/functions-array.html). This has the added bonus that I can store stats about each email address seen with them. (Haven't tried it yet next on the list to do) On Sat, Aug 08, 2009 at 02:10:18PM +0100, Sam Mason wrote: > 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/ > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
