Confused about CASE

Thomas Kellerer <spam_eater@xxxxxxx> · Sat, 01 Mar 2008 00:42:36 +0100

Hello,

I was writing a statement retrieve dependency information out of the system 
catalog, when I noticed something that I didn't expect.

I wanted to use the following statement to "translate" the relkind column to a 
more descriptive value:

select c.relname
       case
         when c.relkind in ('t','r') then 'table'
         when c.relkind = 'i' then 'index'
         when c.relkind = 'S' then 'sequence'
         when c.relkind = 'v' then 'view'
         else c.relkind
       end as mykind
from pg_class c
;

The idea is that for anything else than 't', 'r', 'i', 'S' or 'v' it should 
simply return the value of relkind. In the other cases I want "my" value.

But for some reason this returns the value of relkind for all rows. When I 
remove the "else c.relkind" part, it works as expected.

My understanding of CASE is, that it will return the value of the first 
expresion that evaluates to true. The ELSE part is only returned if all previous 
expressions do not match.

When using it agains a simple test-table (int, char(1)), it's working as 
expected. So this seems to be related to the pg_class table.

So what am I missing here?

I am using 8.2.5 on Windows XP

Thanks in advance
Thomas

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org/