I have a query:
SELECT bar, (SELECT name FROM names WHERE value = bar) as name
FROM foobar WHERE foo = 1 and bar = ANY (1,2,3)
My problem is, when there is no row containing bar = 3 (or whatever other value is requested) in table foobar, no rows are returned for that value of bar.
I'd like my query to return a row of [bar, NULL] instead, but can't think up a way to approach this.
Is this even possible?
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;