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.steh...@gmail.com>wrote: > Hello > > http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#LIKE_to_list_of_patterns > > Regards > > Pavel Stehule > > > 2013/8/14 Tim Kane <tim.k...@gmail.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) >> > >
