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