pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDocumentation for CASE
4+ messages / 3 participants
[nested] [flat]
* Documentation for CASE
@ 2000-12-16 17:00 Albert REINER <areiner@tph.tuwien.ac.at>
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:39 Peter Eisentraut <peter_e@gmx.net>
parent: 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 23:01 Robert B. Easter <reaster@comptechnews.com>
parent: Peter Eisentraut <peter_e@gmx.net>
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-17 14:56 Albert REINER <areiner@tph.tuwien.ac.at>
parent: Peter Eisentraut <peter_e@gmx.net>
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