From b62f246fb0c9897fe5ac294c6f6e75ac2651307f Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 15 May 2008 22:39:49 +0000 Subject: Support SQL/PSM-compatible CASE statement in plpgsql. Pavel Stehule --- doc/src/sgml/errcodes.sgml | 19 ++++++-- doc/src/sgml/plpgsql.sgml | 111 +++++++++++++++++++++++++++++++++++++++++++-- 2 files changed, 122 insertions(+), 8 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/errcodes.sgml b/doc/src/sgml/errcodes.sgml index a3f56a86b14..474c0ca8da7 100644 --- a/doc/src/sgml/errcodes.sgml +++ b/doc/src/sgml/errcodes.sgml @@ -1,4 +1,4 @@ - + <productname>PostgreSQL</productname> Error Codes @@ -62,14 +62,14 @@ - - + + Error Code Meaning - Constant + Condition Name @@ -292,6 +292,17 @@ + +Class 20 — Case Not Found + + + +20000 +CASE NOT FOUND +case_not_found + + + Class 21 — Cardinality Violation diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 09ad6944dba..d2853d2d901 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,4 +1,4 @@ - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -1581,9 +1581,9 @@ SELECT * FROM getallfoo(); Conditionals - IF statements let you execute commands based on - certain conditions. PL/pgSQL has five forms of - IF: + IF and CASE statements let you execute + alternative commands based on certain conditions. + PL/pgSQL has five forms of IF: IF ... THEN @@ -1601,6 +1601,22 @@ SELECT * FROM getallfoo(); IF ... THEN ... ELSEIF ... THEN ... ELSE + + and four forms of CASE: + + + CASE ... WHEN ... THEN ... END CASE + + + CASE ... WHEN ... THEN ... ELSE ... END CASE + + + CASE WHEN ... THEN ... END CASE + + + CASE WHEN ... THEN ... ELSE ... END CASE + + @@ -1751,6 +1767,93 @@ END IF; ELSEIF is an alias for ELSIF. + + + Simple <literal>CASE</> + + +CASE search-expression + WHEN expression , expression ... THEN + statements + WHEN expression , expression ... THEN + statements + ... + ELSE + statements +END CASE; + + + + The simple form of CASE provides conditional execution + based on equality of operands. The search-expression + is evaluated (once) and successively compared to each + expression in the WHEN clauses. + If a match is found, then the corresponding + statements are executed, and then control + passes to the next statement after END CASE. (Subsequent + WHEN expressions are not evaluated.) If no match is + found, the ELSE statements are + executed; but if ELSE is not present, then a + CASE_NOT_FOUND exception is raised. + + + + Here is a simple example: + + +CASE x + WHEN 1, 2 THEN + msg := 'one or two'; + ELSE + msg := 'other value than one or two'; +END CASE; + + + + + + Searched <literal>CASE</> + + +CASE + WHEN boolean-expression THEN + statements + WHEN boolean-expression THEN + statements + ... + ELSE + statements +END CASE; + + + + The searched form of CASE provides conditional execution + based on truth of boolean expressions. Each WHEN clause's + boolean-expression is evaluated in turn, + until one is found that yields true. Then the + corresponding statements are executed, and + then control passes to the next statement after END CASE. + (Subsequent WHEN expressions are not evaluated.) + If no true result is found, the ELSE + statements are executed; + but if ELSE is not present, then a + CASE_NOT_FOUND exception is raised. + + + + Here is an example: + + +CASE + WHEN x BETWEEN 0 AND 10 THEN + msg := 'value is between zero and ten'; + WHEN x BETWEEN 11 AND 20 THEN + msg := 'value is between eleven and twenty'; +END CASE; + + + + -- cgit v1.2.3