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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

  From Date Subject
Next Message Adam Rich 2008-02-29 23:57:24 Re: Confused about CASE
Previous Message pw 2008-02-29 23:30:27 Querying Headers