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 [EMAIL PROTECTED] ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------