Perhaps something like this approach is what you are after:
testbed:
create view names as
select 1 as value, 'Adam' as name union all select 2, 'Beth';
create view foobar as
select 1 as foo, 1 as bar union all select 1, 2;
original method:
select bar, (select name from names where value = bar) as name
from foobar
where foo = 1 and bar = any (array[1, 2, 3]);
bar | name
-----+------
1 | Adam
2 | Beth
(2 rows)
alternative method:
with w as (select unnest(array[1, 2, 3]) as bar)
select bar, (select name from names where value = bar) as name
from w left outer join foobar using(bar);
bar | name
-----+------
1 | Adam
2 | Beth
3 |
(3 rows)
If you are on 8.3 or before, there is no built-in unnest function, but you can roll your own (not very efficient) replacement:
create or replace function unnest(anyarray) returns setof anyelement as $$
select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) i;
$$ language 'sql' immutable;
left outer joinwill join as you request. Second, arrays are not really made to be searched through like mini tables. If you do not know the index of the array there is a good chance you are misusing them. – nate c Mar 30 '11 at 23:55