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

Re: Confused about CASE

From: "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net>
To: "'Thomas Kellerer'" <spam_eater(at)gmx(dot)net>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: Confused about CASE
Date: 2008-02-29 23:57:24
Message-ID: 031301c87b2e$d4951030$7dbf3090$@r@sbcglobal.net (view raw or flat)
Thread:
Lists: pgsql-general
> 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.


I agree, this seems confusing.  I found a section of the doc that caught my
eye:

"The data types of all the result expressions must be convertible to a
single output type."

Which led me to try this, which works:

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::text
        end as mykind
from pg_class c



In response to

Responses

pgsql-general by date

Next:From: Stephan SzaboDate: 2008-03-01 00:00:17
Subject: Re: Confused about CASE
Previous:From: Thomas KellererDate: 2008-02-29 23:42:36
Subject: Confused about CASE

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