Re: Confused about CASE

["Adam Rich" <adam(dot)r(at)sbcglobal(dot)net>]

> 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