Re: Arrays and LIKE

David <david(at)vanlaatum(dot)id(dot)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°