I have an array: ARRAY['one','mon','uuuu','wed','thu','fri','qwer'] and query:
select * from table t where t.name like ARRAY[i]
how could I iterate query with ARRAY values and save results into some output?
I mean to get results of:
select * from table t where t.name like 'one'
select * from table t where t.name like 'mon'
select * from table t where t.name like 'uuuu'
...
select * from table t where t.name like 'qwer'
and save them for example into the output
One option is to unnest the array in a subquery or CTE and apply the expression you want, e.g.
CREATE TEMPORARY TABLE t (name text);
INSERT INTO t VALUES ('mon'),('qwer'),('mon'),('xpto');
WITH j (val) AS (
SELECT UNNEST(ARRAY['one','mon','uuuu','wed','thu','fri','qwer'])
) SELECT * FROM j,t WHERE t.name LIKE j.val;
val | name
------+------
mon | mon
mon | mon
qwer | qwer
(3 Zeilen)
If you need to use % in your LIKE, just concatenate it to the string with ||, e.g.
WITH j (val) AS (
SELECT UNNEST(ARRAY['one','mon','uuuu','wed','thu','fri','qwer'])
) SELECT * FROM j,t WHERE t.name LIKE '%' || j.val || '%';
Or use the ANY operator
SELECT * FROM t
WHERE name LIKE ANY(ARRAY['one','mon','uuuu','wed','thu','fri','qwer']);
name
------
mon
qwer
mon
(3 Zeilen)