Re: [SQL] Documentation for CASE

2000-12-17 Thread Albert REINER

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   <[EMAIL PROTECTED]>
Deutsch   *   English   *   Esperanto   *   Latine
--



Re: [SQL] Documentation for CASE

2000-12-16 Thread Robert B. Easter

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/ 



Re: [SQL] Documentation for CASE

2000-12-16 Thread Peter Eisentraut

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  [EMAIL PROTECTED]   http://yi.org/peter-e/




[SQL] Documentation for CASE

2000-12-16 Thread Albert REINER

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   <[EMAIL PROTECTED]>
Deutsch   *   English   *   Esperanto   *   Latine
--