Re: Documentation for CASE - Mailing list pgsql-sql

From Robert B. Easter
Subject Re: Documentation for CASE
Date
Msg-id 00121618014902.22968@comptechnews
Whole thread Raw
In response to Re: Documentation for CASE  (Peter Eisentraut <peter_e@gmx.net>)
List pgsql-sql
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/ ------------


pgsql-sql by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Documentation for CASE
Next
From: "Albert REINER"
Date:
Subject: Re: Documentation for CASE