pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Documentation for CASE
4+ messages / 3 participants
[nested] [flat]

* Documentation for CASE
@ 2000-12-16 17:00 Albert REINER <areiner@tph.tuwien.ac.at>
  2000-12-16 17:39 ` Re: Documentation for CASE Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 1 reply; 4+ messages in thread

From: Albert REINER @ 2000-12-16 17:00 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Documentation for CASE
  2000-12-16 17:00 Documentation for CASE Albert REINER <areiner@tph.tuwien.ac.at>
@ 2000-12-16 17:39 ` Peter Eisentraut <peter_e@gmx.net>
  2000-12-16 23:01   ` Re: Documentation for CASE Robert B. Easter <reaster@comptechnews.com>
  2000-12-17 14:56   ` Re: Documentation for CASE Albert REINER <areiner@tph.tuwien.ac.at>
  0 siblings, 2 replies; 4+ messages in thread

From: Peter Eisentraut @ 2000-12-16 17:39 UTC (permalink / raw)
  To: Albert REINER <areiner@tph.tuwien.ac.at>; +Cc: pgsql-sql

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/




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Documentation for CASE
  2000-12-16 17:00 Documentation for CASE Albert REINER <areiner@tph.tuwien.ac.at>
  2000-12-16 17:39 ` Re: Documentation for CASE Peter Eisentraut <peter_e@gmx.net>
@ 2000-12-16 23:01   ` Robert B. Easter <reaster@comptechnews.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Robert B. Easter @ 2000-12-16 23:01 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; Albert REINER <areiner@tph.tuwien.ac.at>; +Cc: 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/ ------------



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Documentation for CASE
  2000-12-16 17:00 Documentation for CASE Albert REINER <areiner@tph.tuwien.ac.at>
  2000-12-16 17:39 ` Re: Documentation for CASE Peter Eisentraut <peter_e@gmx.net>
@ 2000-12-17 14:56   ` Albert REINER <areiner@tph.tuwien.ac.at>
  1 sibling, 0 replies; 4+ messages in thread

From: Albert REINER @ 2000-12-17 14:56 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2000-12-17 14:56 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-16 17:00 Documentation for CASE Albert REINER <areiner@tph.tuwien.ac.at>
2000-12-16 17:39 ` Peter Eisentraut <peter_e@gmx.net>
2000-12-16 23:01   ` Robert B. Easter <reaster@comptechnews.com>
2000-12-17 14:56   ` Albert REINER <areiner@tph.tuwien.ac.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox