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

Re: Confused about CASE

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Thomas Kellerer <spam_eater(at)gmx(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Confused about CASE
Date: 2008-03-01 00:00:17
Message-ID: 20080229155244.L9649@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-general
On Sat, 1 Mar 2008, Thomas Kellerer wrote:

> 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.

Actually, it doesn't exactly in my tests... for sequences it will
apparently return 's' not 'S'.

It looks like the problem is that relkind is of the somewhat odd
PostgreSQL type "char" not an actual char(1), so with the else in there it
appears to try to force the unknown literals into that type which only
takes the first character. It will probably work if you cast in the else,
like "else CAST(c.relkind as CHAR(1))".

In response to

Responses

pgsql-general by date

Next:From: Adam RichDate: 2008-03-01 00:02:01
Subject: Re: Confused about CASE
Previous:From: Adam RichDate: 2008-02-29 23:57:24
Subject: Re: Confused about CASE

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