Re: Arrays and LIKE

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--