Thread: Documentation for CASE

Saluton,

I think that the documentation for CASE is not very clear:

,----[ postgres/doc.html/user/functions.htm#AEN2546 ]
|    Table 5-1. SQL Functions
|    Function Returns Description Example
| ...
|    CASE  WHEN  expr  THEN expr [...] ELSE expr END expr return expression
|    for  first  true  WHEN  clause  CASE WHEN c1 = 1 THEN 'match' ELSE 'no
|    match' END
`----

When building a statement with more than one clause, I was tempted to
write CASE WHEN a THEN b ELSE WHEN c THEN d ELSE e instead of the
correct CASE WHEN a THEN b WHEN c THEN d ELSE e.

Also, to be correct, the description should mention what the ELSE
clause is good for (though it's not difficult to guess).

I'm sorry to have bothered you if this was already corrected for more
recent versions - I'm running

,----
| albert=> select version();
|                 version
| --------------------------------------------------------------------
|  PostgreSQL 7.0.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
| (1 row)
`----

Bye,

Albert.


-- 

--------------------------------------------------------------------------
Albert Reiner                                   <areiner@tph.tuwien.ac.at>
Deutsch       *       English       *       Esperanto       *       Latine
--------------------------------------------------------------------------

Albert REINER writes:

> I think that the documentation for CASE is not very clear:

Maybe you will like this better (from upcoming 7.1):

http://www.postgresql.org/devel-corner/docs/postgres/functions-conditional.htm

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/

Here are some examples of CASE used in PL/pgSQL for both an UPDATE and an 
assignment:

                      NEW.tscore := round( (NEW.treply_scores + (CASE WHEN 
NEW.scored = ''f'' THEN 0 ELSE NEW.score END) )::FLOAT / (NEW.treplies + 1) );               END IF;               IF
NEW.tscore!= OLD.tscore OR NEW.score != OLD.score THEN                       IF OLD.scored = ''f'' THEN
             OLD.score := 0;                       END IF;                       IF NEW.scored = ''f'' THEN
                 NEW.score := 0;                       END IF;                       UPDATE users SET score = score
                                         - (CASE WHEN OLD.tscore > 
 
OLD.score THEN OLD.tscore ELSE OLD.score END)                                               + (CASE WHEN NEW.tscore > 
NEW.score THEN NEW.tscore ELSE NEW.score END)                               WHERE id = NEW.users_id;
  IF NEW.scored = ''f'' THEN                                     
 


Kind of cool the way PostgreSQL functions and some other SQL language 
elements etc can be used in assignments too.


On Saturday 16 December 2000 12:39, Peter Eisentraut wrote:
> Albert REINER writes:
> > I think that the documentation for CASE is not very clear:
>
> Maybe you will like this better (from upcoming 7.1):
>
> http://www.postgresql.org/devel-corner/docs/postgres/functions-conditional.
>htm

-- 
-------- Robert B. Easter  reaster@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------

On Sat, Dec 16, 2000 at 06:39:14PM +0100, Peter Eisentraut wrote:
> Albert REINER writes:
> 
> > I think that the documentation for CASE is not very clear:
> 
> Maybe you will like this better (from upcoming 7.1):
> 
> http://www.postgresql.org/devel-corner/docs/postgres/functions-conditional.htm

much better. Sorry not to have checked that first.

Albert.

-- 

--------------------------------------------------------------------------
Albert Reiner                                   <areiner@tph.tuwien.ac.at>
Deutsch       *       English       *       Esperanto       *       Latine
--------------------------------------------------------------------------