Default value if query returns 0 rows?

From: Lars Kellogg-Stedman <lars(at)oddbit(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Default value if query returns 0 rows?
Date: 2004-09-17 15:03:48
Message-ID: Pine.LNX.4.44.0409171056390.8060-100000@wolery.deas.harvard.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

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

I think I can see how to do this by writing a pl/pgsql function, but I'm
curious if it's possible to do this completely as part of a select
statement. I've toyed with CASE expressions, but the fact that a missing
value returns 0 rows continues to foil me.

Thanks,

-- Lars

--
Lars Kellogg-Stedman <lars(at)oddbit(dot)com>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2004-09-17 15:31:51 Re: Default value if query returns 0 rows?
Previous Message Tom Lane 2004-09-17 14:56:36 Re: Converting varchar() to text