Lars Kellogg-Stedman <[EMAIL PROTECTED]> writes:
> I have a simple two-column table mapping names to ids. I'd like to write a
> select statement that will return a default value if a given name isn't
> found in the table. That is, I want something equivalent to the following
> pseudocode:
> if exists (select 1 from map where name = 'foo') then
> select id from map where name = 'foo'
> else
> select -1
> end if
Is the name unique? If so you could do
select * from
(select id from map where name = 'foo'
union all
select -1) ss
limit 1;
This is an abuse of SQL of course --- mainly, it relies on the
assumption that UNION ALL is implemented in the "obvious" way.
But it certainly will work in current and foreseeable versions
of Postgres. A bigger problem is that I don't see how to extend
the approach if there might be more than one 'foo' row, and you
want them all and only want the -1 when there are none.
Another way is a subselect:
select coalesce((select id from map where name = 'foo'), -1);
but this one will actively blow up if there are multiple 'foo' rows,
so it doesn't solve that problem either.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
