Arrays and LIKE

David <david@vanlaatum.id.au> 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)[]

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...

test=*# \d foo
Tabelle �public.foo�
Spalte | Typ | Attribute
--------+--------+-----------
t | text[] |

test=*# select * from foo;
t
-----------------
{foo,bla,blub}
{xyz,bla,fasel}
(2 Zeilen)

Zeit: 0,393 ms
test=*# select * from ( select t, generate_subscripts(t, 1) AS s from foo) bla where t[s] ~ '^xy.*';
t | s
-----------------+---
{xyz,bla,fasel} | 1
(1 Zeile)

(http://www.postgresql.org/docs/8.4/interactive/arrays.html#ARRAYS-SEARCHING)

But i don't know how to create an index for that...

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

On 2009-08-08, David <david@vanlaatum.id.au> 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)[]

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...

hmm tricky: you can't use any(recipients) ilike 'david%'

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/

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

--