Re: WHERE 'Something%' LIKE ANY (array_field)

Thanks Pavel,

Unless I'm being bleary eyed and not quite grasping it... I'm not sure
that answers my question.
I'm using a single LIKE clause against an array parameter, rather than
multiple LIKE clauses against a single parameter.

It seems I'm so far stuck with a FOREACH style traversal within plpgsql
(which is fine, as this is all to be used within a function anyway).

On Wed, Aug 14, 2013 at 10:55 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> Hello
>
> http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#LIKE_to_list_of_patterns
>
> Regards
>
> Pavel Stehule
>
>
> 2013/8/14 Tim Kane <tim(dot)kane(at)gmail(dot)com>
>
>> Hi all,
>>
>> It seems like it isn't possible to perform a wildcard LIKE evaluation
>> against array objects. Is this a bug, or just unsupported?
>>
>>
>> See the queries in bold, that I would have expected to return a value.
>> Postgres 9.1.9
>>
>>
>> =# create temp table ids (id varchar(12)[]);
>> CREATE TABLE
>> Time: 185.516 ms
>> =# insert into ids values ('{s1,s452334,s89}');
>> INSERT 0 1
>> Time: 0.728 ms
>> =# insert into ids values ('{s89}');
>> INSERT 0 1
>> Time: 0.300 ms
>> =# insert into ids values ('{s9323,s893}');
>> INSERT 0 1
>> Time: 0.133 ms
>> =# insert into ids values ('{s9323,s893,s89}');
>> INSERT 0 1
>> Time: 0.110 ms
>> =# select * from ids;
>> id
>> ------------------
>> {s1,s452334,s89}
>> {s89}
>> {s9323,s893}
>> {s9323,s893,s89}
>> (4 rows)
>>
>> Time: 0.155 ms
>> =# select * from ids where 's89' = ANY (id);
>> id
>> ------------------
>> {s1,s452334,s89}
>> {s89}
>> {s9323,s893,s89}
>> (3 rows)
>>
>> Time: 0.121 ms
>> *clone=# select * from ids where 's45%' LIKE ANY (id);*
>> id
>> ----
>> (0 rows)
>>
>> Time: 0.124 ms
>>
>> *clone=# select * from ids where 's452334%' LIKE ANY (id);*
>> id
>> ----
>> (0 rows)
>>
>> Time: 0.278 ms
>> clone=# select * from ids where 's452334' LIKE ANY (id);
>> id
>> ------------------
>> {s1,s452334,s89}
>> (1 row)
>>
>> Time: 0.134 ms
>> clone=# select * from ids where 's452334' = ANY (id);
>> id
>> ------------------
>> {s1,s452334,s89}
>> (1 row)
>>
>
>