DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=JtOSCibJqdE4PxXC1jgoHQgoyAcHsKx/h6e5MrBYfw8mHhsFvo7mIgUnKcItoRywLAFWhznsn0EqwyL+t80UQv2cYSKpQAQnXYfLquVIRGTao84anZM/blKnypbHB4heY+zCxP0hSARks6YPGIb2h0OILSQ+m2c87P3RZY4s0VM=
	;
Message-ID: <20060608043236.96435.qmail@web31810.mail.mud.yahoo.com>
Date: Wed, 7 Jun 2006 21:32:36 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Query to return modified results at runtime?
To: George Handin <postgresql@dafunks.com>, pgsql-sql@postgresql.org
In-Reply-To: <44876F63.9030302@dafunks.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

> ID    Color
> ---   -------
> 1     Blue
> 2     Red
> 3     Green
> 4     Orange
> 
> How would I rewrite the query to return results where the colors are 
> replaced by letters to give the following results?
> 
> ID    Color
> ---   -------
> 1     A
> 2     D
> 3     B
> 4     C


http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html

this is probably the easiest to implement but hard to mangage over time.  Another solution would
be to create color_code table that is referenced by your test table.  Then when you can create a
query as: select a.ID, b.code from test as a join color_code as b on a.color = b.color;

There are additional solutions to this also. But these two are probably the easiest.