Skip site navigation (1) Skip section navigation (2)

Confused about CASE

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Confused about CASE
Date: 2008-02-29 23:42:36
Message-ID: fqa59d$p83$1@ger.gmane.org (view raw or flat)
Thread:
Lists: pgsql-general
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



Responses

pgsql-general by date

Next:From: Adam RichDate: 2008-02-29 23:57:24
Subject: Re: Confused about CASE
Previous:From: pwDate: 2008-02-29 23:30:27
Subject: Querying Headers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group