Hello
I am sending little bit smarter version - without redundant parsing.
When test expression is defined, then expressions in WHEN part are
modified like
$x in ( origin_expression )
$x is referenced to invisible *case* variable that carries result of
test expression.
Regards
Pavel Stehule
2008/5/3 Pavel Stehule <[EMAIL PROTECTED]>:
> Hello
>
> 2008/5/3 Tom Lane <[EMAIL PROTECTED]>:
>> "Pavel Stehule" <[EMAIL PROTECTED]> writes:
>>> 2008/5/2 Heikki Linnakangas <[EMAIL PROTECTED]>:
>>>> How about taking a completely different strategy, and implement the
>>>> CASE-WHEN construct fully natively in plpgsql, instead of trying to convert
>>>> it to a single SQL CASE-WHEN expression? It's not a very good match anyway;
>>
>>> It was first variant. It's simpler for parsing and slower for
>>> execution :(. It means more than once expression evaluation and for
>>> simple case value casting and comparation.
>>
>> I agree with Heikki: this patch is seriously ugly, and "slower for
>> execution" isn't a good enough reason for saddling us with having
>> to maintain such a kluge in the parser.
>>
>> I don't really see why you should need to have multiple expression
>> evaluations, anyhow. Can't you evaluate the test expression once
>> and inject its value into the comparisons using CaseTestExpr,
>> the same way the core CASE-expression code works?
>>
>>
>
> I have to look on this way.
>
> Regards
> Pavel Stehule
>
> regards, tom lane
>>
>
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-05-03 02:11:36.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml 2008-05-06 11:05:05.000000000 +0200
***************
*** 1601,1606 ****
--- 1601,1622 ----
<para><literal>IF ... THEN ... ELSEIF ... THEN ... ELSE</></>
</listitem>
</itemizedlist>
+
+ and four forms of <literal>CASE</>:
+ <itemizedlist>
+ <listitem>
+ <para><literal>CASE ... WHEN ... THEN ... END CASE</></>
+ </listitem>
+ <listitem>
+ <para><literal>CASE ... WHEN ... THEN ... ELSE ... END CASE</></>
+ </listitem>
+ <listitem>
+ <para><literal>CASE WHEN ... THEN ... END CASE</></>
+ </listitem>
+ <listitem>
+ <para><literal>CASE WHEN ... THEN ... ELSE ... END CASE</></>
+ </listitem>
+ </itemizedlist>
</para>
<sect3>
***************
*** 1751,1756 ****
--- 1767,1838 ----
<literal>ELSEIF</> is an alias for <literal>ELSIF</>.
</para>
</sect3>
+
+ <sect3>
+ <title>Simple <literal>CASE</> statement</title>
+ <synopsis>
+ CASE <replaceable>expression</replaceable>
+ WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
+ <replaceable>statements</replaceable>
+ <optional> WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
+ <replaceable>statements</replaceable>
+ <optional> WHEN <replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> <optional> ... </optional></optional> THEN
+ <replaceable>statements</replaceable>
+ ... </optional></optional>
+ <optional> ELSE
+ <replaceable>statements</replaceable> </optional>
+ END CASE;
+ </synopsis>
+ <para>
+ Provide conditional execution based on equality of operands. If no case is matched,
+ then is ELSE clause executed. If statement doesn't contains ELSE clause,
+ then <literal>CASE_NOT_FOUND</literal> exception is raised.
+ </para>
+ <para>Here is example:
+ <programlisting>
+ CASE a
+ WHEN 1, 2 THEN
+ msg := 'one or two';
+ ELSE
+ msg := 'other value than one or two';
+ END CASE;
+ </programlisting>
+ </para>
+ </sect3>
+
+ <sect3>
+ <title>Searched <literal>CASE</> statement</title>
+ <synopsis>
+ CASE
+ WHEN <replaceable>boolean-expression</replaceable> THEN
+ <replaceable>statements</replaceable>
+ <optional> WHEN <replaceable>boolean-expression</replaceable> THEN
+ <replaceable>statements</replaceable>
+ <optional> WHEN <replaceable>boolean-expression</replaceable> THEN
+ <replaceable>statements</replaceable>
+ ... </optional></optional>
+ <optional> ELSE
+ <replaceable>statements</replaceable> </optional>
+ END CASE;
+ </synopsis>
+ <para>
+ Provide conditional execution based on truth of
+ <replaceable>boolean-expression</replaceable>. If no case is matched,
+ then is ELSE clause executed. If statement doesn't contains ELSE clause,
+ then <literal>CASE_NOT_FOUND</literal> exception is raised.
+ </para>
+ <para> Here is example:
+ <programlisting>
+ CASE
+ WHEN a BETWEEN 0 AND 10 THEN
+ msg := 'value is between zero and ten';
+ WHEN a BETWEEN 11 AND 20 THEN
+ msg := 'value is between eleven and twenty';
+ END CASE;
+ </programlisting>
+ </para>
+
+ </sect3>
</sect2>
<sect2 id="plpgsql-control-structures-loops">
*** ./src/backend/catalog/sql_feature_packages.txt.orig 2008-05-06 11:01:18.000000000 +0200
--- ./src/backend/catalog/sql_feature_packages.txt 2008-05-06 11:05:05.000000000 +0200
***************
*** 41,46 ****
--- 41,48 ----
F671 Enhanced integrity management
F701 Enhanced integrity management
F812 Core
+ P004 PSM
+ P008 PSM
S011 Core
S023 Basic object support
S024 Enhanced object support
*** ./src/backend/catalog/sql_features.txt.orig 2008-05-06 11:01:27.000000000 +0200
--- ./src/backend/catalog/sql_features.txt 2008-05-06 11:05:05.000000000 +0200
***************
*** 297,302 ****
--- 297,304 ----
F831 Full cursor update NO
F831 Full cursor update 01 Updatable scrollable cursors NO
F831 Full cursor update 02 Updatable ordered cursors NO
+ P004 Extended CASE statement YES
+ P008 Comma-separated predicates in simple CASE statement YES
S011 Distinct data types NO
S011 Distinct data types 01 USER_DEFINED_TYPES view NO
S023 Basic structured types NO
*** ./src/include/utils/errcodes.h.orig 2008-05-06 11:01:47.000000000 +0200
--- ./src/include/utils/errcodes.h 2008-05-06 11:05:05.000000000 +0200
***************
*** 107,112 ****
--- 107,113 ----
/* Class 22 - Data Exception */
#define ERRCODE_DATA_EXCEPTION MAKE_SQLSTATE('2','2', '0','0','0')
+ #define ERRCODE_CASE_NOT_FOUND ERRCODE_DATA_EXCEPTION
#define ERRCODE_ARRAY_ELEMENT_ERROR MAKE_SQLSTATE('2','2', '0','2','E')
/* SQL99's actual definition of "array element error" is subscript error */
#define ERRCODE_ARRAY_SUBSCRIPT_ERROR ERRCODE_ARRAY_ELEMENT_ERROR
*** ./src/pl/plpgsql/src/gram.y.orig 2008-05-03 02:11:36.000000000 +0200
--- ./src/pl/plpgsql/src/gram.y 2008-05-06 23:54:42.000000000 +0200
***************
*** 18,24 ****
#include "parser/parser.h"
-
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
int until3,
--- 18,23 ----
***************
*** 37,42 ****
--- 36,43 ----
static PLpgSQL_stmt *make_return_stmt(int lineno);
static PLpgSQL_stmt *make_return_next_stmt(int lineno);
static PLpgSQL_stmt *make_return_query_stmt(int lineno);
+ static PLpgSQL_stmt *make_case(int lineno, PLpgSQL_expr *case_expr,
+ List *case_when_list, List *else_stmts);
static void check_assignable(PLpgSQL_datum *datum);
static void read_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row,
bool *strict);
***************
*** 101,106 ****
--- 102,108 ----
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
PLpgSQL_stmt_fetch *fetch;
+ PLpgSQL_case_when *casewhen;
}
%type <declhdr> decl_sect
***************
*** 115,121 ****
%type <str> decl_stmts decl_stmt
%type <expr> expr_until_semi expr_until_rightbracket
! %type <expr> expr_until_then expr_until_loop
%type <expr> opt_exitcond
%type <ival> assign_var
--- 117,123 ----
%type <str> decl_stmts decl_stmt
%type <expr> expr_until_semi expr_until_rightbracket
! %type <expr> expr_until_then expr_until_loop opt_expr_until_when
%type <expr> opt_exitcond
%type <ival> assign_var
***************
*** 134,145 ****
--- 136,150 ----
%type <stmt> stmt_return stmt_raise stmt_execsql stmt_execsql_insert
%type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_move stmt_close stmt_null
+ %type <stmt> stmt_case
%type <list> proc_exceptions
%type <exception_block> exception_sect
%type <exception> proc_exception
%type <condition> proc_conditions
+ %type <casewhen> case_when
+ %type <list> case_when_list opt_case_default
%type <ival> raise_level
%type <str> raise_msg
***************
*** 160,165 ****
--- 165,171 ----
%token K_ASSIGN
%token K_BEGIN
%token K_BY
+ %token K_CASE
%token K_CLOSE
%token K_CONSTANT
%token K_CONTINUE
***************
*** 618,623 ****
--- 624,631 ----
{ $$ = $1; }
| stmt_if
{ $$ = $1; }
+ | stmt_case
+ { $$ = $1; }
| stmt_loop
{ $$ = $1; }
| stmt_while
***************
*** 816,821 ****
--- 824,868 ----
}
;
+ stmt_case : K_CASE lno opt_expr_until_when case_when_list opt_case_default K_END K_CASE ';'
+ {
+ $$ = make_case($2, $3, $4, $5);
+ plpgsql_ns_pop();
+ }
+ ;
+
+ opt_case_default :
+ {
+ $$ = NIL;
+ }
+ | K_ELSE proc_stmts
+ {
+ $$ = $2;
+ }
+ ;
+
+ case_when_list : case_when_list case_when
+ {
+ $$ = lappend($1, $2);
+ }
+ | case_when
+ {
+ $$ = list_make1($1);
+ }
+ ;
+
+ case_when : K_WHEN lno expr_until_then proc_stmts
+ {
+ PLpgSQL_case_when *new = palloc(sizeof(PLpgSQL_case_when));
+
+ new->lineno = $2;
+ new->expr = $3;
+ new->stmts = $4;
+
+ $$ = new;
+ }
+ ;
+
stmt_loop : opt_block_label K_LOOP lno loop_body
{
PLpgSQL_stmt_loop *new;
***************
*** 1624,1629 ****
--- 1671,1691 ----
{ $$ = plpgsql_read_expression(K_LOOP, "LOOP"); }
;
+ opt_expr_until_when :
+ {
+ PLpgSQL_expr *expr = NULL;
+ int tok = yylex();
+
+ if (tok != K_WHEN)
+ {
+ plpgsql_push_back_token(tok);
+ expr = plpgsql_read_expression(K_WHEN, "WHEN");
+ }
+ plpgsql_push_back_token(K_WHEN);
+ $$ = expr;
+ }
+ ;
+
opt_block_label :
{
plpgsql_ns_push(NULL);
***************
*** 1636,1641 ****
--- 1698,1704 ----
}
;
+
/*
* need all the options because scanner will have tried to resolve as variable
*/
***************
*** 2659,2664 ****
--- 2722,2810 ----
}
+ /*
+ * CASE statement
+ * When test expr is defined, then we inject WHEN expression as $(n+1) in (expression).
+ */
+ static PLpgSQL_stmt *
+ make_case(int lineno, PLpgSQL_expr *t_expr,
+ List *case_when_list, List *else_stmts)
+ {
+ PLpgSQL_stmt_case *new;
+
+ new = palloc(sizeof(PLpgSQL_stmt_case));
+ new->cmd_type = PLPGSQL_STMT_CASE;
+ new->lineno = lineno;
+ new->t_expr = t_expr;
+ new->case_when_list = case_when_list;
+ new->else_stmts = else_stmts;
+
+ /*
+ * Because I wouldn't generate plan of test_expr for
+ * getting its result's type, I expect integer as result.
+ * I can be corrected it later.
+ */
+ if (t_expr)
+ {
+ ListCell *l;
+ int t_varno;
+
+ new->t_var = (PLpgSQL_var *) plpgsql_build_variable("*case*", lineno,
+ plpgsql_build_datatype(INT4OID, -1),
+ false);
+ t_varno =new->t_var->varno;
+
+ foreach(l, case_when_list)
+ {
+ PLpgSQL_dstring ds;
+ PLpgSQL_case_when *cwt = (PLpgSQL_case_when *) lfirst(l);
+ PLpgSQL_expr *expr = cwt->expr;
+ PLpgSQL_expr *new_expr;
+ int nparams = expr->nparams;
+ char buff[32];
+
+ if (nparams >= MAX_EXPR_PARAMS)
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("too many variables specified in SQL statement")));
+ }
+
+ new_expr = palloc(sizeof(PLpgSQL_expr) + sizeof(int) * (nparams + 1) - sizeof(int));
+ new_expr->dtype = expr->dtype;
+ new_expr->nparams = nparams + 1;
+ new_expr->plan = NULL;
+
+ memcpy(new_expr->params, expr->params, nparams * sizeof(int));
+ new_expr->params[nparams] = t_varno;
+
+ plpgsql_dstring_init(&ds);
+
+ plpgsql_dstring_append(&ds, "SELECT $");
+ snprintf(buff, sizeof(buff), "%d", nparams + 1);
+ plpgsql_dstring_append(&ds, buff);
+ plpgsql_dstring_append(&ds, " IN (");
+
+ /* copy expression query without SELECT keyword */
+ Assert(strncmp(expr->query, "SELECT ", 7) == 0);
+ plpgsql_dstring_append(&ds, expr->query + 7);
+ plpgsql_dstring_append_char(&ds, ')');
+
+ new_expr->query = pstrdup(plpgsql_dstring_get(&ds));
+
+ plpgsql_dstring_free(&ds);
+ pfree(expr->query);
+ pfree(expr);
+
+ cwt->expr = new_expr;
+ }
+ }
+
+ return (PLpgSQL_stmt *) new;
+ }
+
+
/* Needed to avoid conflict between different prefix settings: */
#undef yylex
*** ./src/pl/plpgsql/src/plerrcodes.h.orig 2008-05-06 11:03:02.000000000 +0200
--- ./src/pl/plpgsql/src/plerrcodes.h 2008-05-06 11:05:05.000000000 +0200
***************
*** 750,752 ****
--- 750,756 ----
{
"index_corrupted", ERRCODE_INDEX_CORRUPTED
},
+
+ {
+ "case_not_found", ERRCODE_CASE_NOT_FOUND
+ },
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2008-05-03 02:11:36.000000000 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2008-05-06 23:09:09.000000000 +0200
***************
*** 95,100 ****
--- 95,102 ----
PLpgSQL_stmt_getdiag *stmt);
static int exec_stmt_if(PLpgSQL_execstate *estate,
PLpgSQL_stmt_if *stmt);
+ static int exec_stmt_case(PLpgSQL_execstate *estate,
+ PLpgSQL_stmt_case *stmt);
static int exec_stmt_loop(PLpgSQL_execstate *estate,
PLpgSQL_stmt_loop *stmt);
static int exec_stmt_while(PLpgSQL_execstate *estate,
***************
*** 1238,1243 ****
--- 1240,1249 ----
rc = exec_stmt_if(estate, (PLpgSQL_stmt_if *) stmt);
break;
+ case PLPGSQL_STMT_CASE:
+ rc = exec_stmt_case(estate, (PLpgSQL_stmt_case *) stmt);
+ break;
+
case PLPGSQL_STMT_LOOP:
rc = exec_stmt_loop(estate, (PLpgSQL_stmt_loop *) stmt);
break;
***************
*** 1429,1434 ****
--- 1435,1530 ----
}
+ /*-----------
+ * case_stmt
+ *
+ *
+ *-----------
+ */
+ static int
+ exec_stmt_case(PLpgSQL_execstate *estate, PLpgSQL_stmt_case *stmt)
+ {
+ bool isnull = true;
+ PLpgSQL_var *t_var = NULL;
+
+ if (stmt->t_expr != NULL)
+ {
+ /* simple case */
+ Datum t_val;
+ Oid t_oid;
+
+ t_val = exec_eval_expr(estate, stmt->t_expr,
+ &isnull,
+ &t_oid);
+ t_var = (PLpgSQL_var *) estate->datums[stmt->t_var->varno];
+
+ /* when expected datatype is other than real, change it */
+ if (stmt->t_var->datatype->typoid != t_oid)
+ {
+ MemoryContext oldcxt;
+ PLpgSQL_type *oldtype = stmt->t_var->datatype;
+
+ pfree(oldtype->typname);
+ pfree(oldtype);
+
+ oldcxt = MemoryContextSwitchTo(estate->err_func->fn_cxt);
+ stmt->t_var->datatype = plpgsql_build_datatype(t_oid, -1);
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ /*
+ * Store value only when is not null, null value means
+ * direct execution of else path.
+ */
+ if (!isnull)
+ exec_assign_value(estate,
+ (PLpgSQL_datum *) t_var,
+ t_val,
+ t_oid,
+ &isnull);
+
+ exec_eval_cleanup(estate);
+ }
+
+ /*
+ * eval paths when test expr result is not null, or test_expr
+ * isn't defined /search case/
+ */
+ if (!isnull || stmt->t_expr == NULL)
+ {
+ ListCell *l;
+ bool value;
+
+ foreach(l, stmt->case_when_list)
+ {
+ PLpgSQL_case_when *cwt = (PLpgSQL_case_when *) lfirst(l);
+
+ value = exec_eval_boolean(estate, cwt->expr, &isnull);
+ exec_eval_cleanup(estate);
+ if (!isnull && value)
+ {
+ if (t_var != NULL)
+ free_var(t_var);
+
+ return exec_stmts(estate, cwt->stmts);
+ }
+ }
+ }
+
+ if (stmt->else_stmts == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_CASE_NOT_FOUND),
+ errmsg("case not found"),
+ errhint("CASE statement missing ELSE part.")));
+
+ if (t_var != NULL)
+ free_var(t_var);
+
+ return exec_stmts(estate, stmt->else_stmts);
+ }
+
+
/* ----------
* exec_stmt_loop Loop over statements until
* an exit occurs.
*** ./src/pl/plpgsql/src/pl_funcs.c.orig 2008-05-03 02:11:36.000000000 +0200
--- ./src/pl/plpgsql/src/pl_funcs.c 2008-05-06 23:29:22.000000000 +0200
***************
*** 526,531 ****
--- 526,532 ----
static void dump_block(PLpgSQL_stmt_block *block);
static void dump_assign(PLpgSQL_stmt_assign *stmt);
static void dump_if(PLpgSQL_stmt_if *stmt);
+ static void dump_case(PLpgSQL_stmt_case *stmt);
static void dump_loop(PLpgSQL_stmt_loop *stmt);
static void dump_while(PLpgSQL_stmt_while *stmt);
static void dump_fori(PLpgSQL_stmt_fori *stmt);
***************
*** 572,577 ****
--- 573,581 ----
case PLPGSQL_STMT_IF:
dump_if((PLpgSQL_stmt_if *) stmt);
break;
+ case PLPGSQL_STMT_CASE:
+ dump_case((PLpgSQL_stmt_case *) stmt);
+ break;
case PLPGSQL_STMT_LOOP:
dump_loop((PLpgSQL_stmt_loop *) stmt);
break;
***************
*** 714,719 ****
--- 718,762 ----
printf(" ENDIF\n");
}
+ static void
+ dump_case(PLpgSQL_stmt_case *stmt)
+ {
+ ListCell *l;
+
+ dump_ind();
+ printf("CASE ");
+ dump_indent += 6;
+ if (stmt->t_expr)
+ {
+ dump_expr(stmt->t_expr);
+ printf("\n");
+ }
+ foreach(l, stmt->case_when_list)
+ {
+ PLpgSQL_case_when *cwt = (PLpgSQL_case_when *) lfirst(l);
+ dump_ind();
+ printf("WHEN ");
+ dump_expr(cwt->expr);
+ printf("\n");
+ dump_ind();
+ printf("THEN\n");
+ dump_indent += 2;
+ dump_stmts(cwt->stmts);
+ dump_indent -= 2;
+ }
+ if (stmt->else_stmts)
+ {
+ dump_ind();
+ printf("ELSE\n");
+ dump_indent += 2;
+ dump_stmts(stmt->else_stmts);
+ dump_indent -= 2;
+ }
+ dump_indent -= 6;
+ dump_ind();
+ printf(" ENDCASE\n");
+ }
+
static void
dump_loop(PLpgSQL_stmt_loop *stmt)
{
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2008-05-03 02:11:36.000000000 +0200
--- ./src/pl/plpgsql/src/plpgsql.h 2008-05-06 23:55:53.000000000 +0200
***************
*** 76,81 ****
--- 76,82 ----
PLPGSQL_STMT_BLOCK,
PLPGSQL_STMT_ASSIGN,
PLPGSQL_STMT_IF,
+ PLPGSQL_STMT_CASE,
PLPGSQL_STMT_LOOP,
PLPGSQL_STMT_WHILE,
PLPGSQL_STMT_FORI,
***************
*** 327,332 ****
--- 328,340 ----
typedef struct
+ {
+ int lineno;
+ PLpgSQL_expr *expr;
+ List *stmts;
+ } PLpgSQL_case_when;
+
+ typedef struct
{ /* Block of statements */
int cmd_type;
int lineno;
***************
*** 377,382 ****
--- 385,401 ----
} PLpgSQL_stmt_if;
+ typedef struct /* CASE statement */
+ {
+ int cmd_type;
+ int lineno;
+ PLpgSQL_expr *t_expr;
+ PLpgSQL_var *t_var;
+ List *case_when_list;
+ List *else_stmts;
+ } PLpgSQL_stmt_case;
+
+
typedef struct
{ /* Unconditional LOOP statement */
int cmd_type;
*** ./src/pl/plpgsql/src/scan.l.orig 2008-05-06 11:03:30.000000000 +0200
--- ./src/pl/plpgsql/src/scan.l 2008-05-06 11:05:05.000000000 +0200
***************
*** 115,120 ****
--- 115,121 ----
alias { return K_ALIAS; }
begin { return K_BEGIN; }
by { return K_BY; }
+ case { return K_CASE; }
close { return K_CLOSE; }
constant { return K_CONSTANT; }
continue { return K_CONTINUE; }
*** ./src/test/regress/expected/plpgsql.out.orig 2008-05-03 02:11:36.000000000 +0200
--- ./src/test/regress/expected/plpgsql.out 2008-05-06 23:38:02.000000000 +0200
***************
*** 3285,3287 ****
--- 3285,3387 ----
(4 rows)
drop function return_dquery();
+ --test CASE statement
+ create or replace FUNCTION case_test(int)
+ returns text as $$
+ declare a int = 10;
+ b int = 1;
+ begin
+ case $1
+ when 1 then
+ return 'one';
+ when 2 then
+ return 'two';
+ when 3,4,3+5 then
+ raise notice 'warning: ambiguous';
+ return 'three, four or five';
+ when a then
+ return 'ten';
+ when a+b, a+1+b then
+ raise notice 'warning: ambiguous';
+ return 'eleven, twelve';
+ end case;
+ end;
+ $$ language plpgsql immutable;
+ select case_test(1);
+ case_test
+ -----------
+ one
+ (1 row)
+
+ select case_test(2);
+ case_test
+ -----------
+ two
+ (1 row)
+
+ select case_test(3);
+ NOTICE: warning: ambiguous
+ case_test
+ ---------------------
+ three, four or five
+ (1 row)
+
+ select case_test(4);
+ NOTICE: warning: ambiguous
+ case_test
+ ---------------------
+ three, four or five
+ (1 row)
+
+ select case_test(5);
+ ERROR: case not found
+ HINT: CASE statement missing ELSE part.
+ CONTEXT: PL/pgSQL function "case_test" line 4 at unknown
+ --raise exception: case not found
+ select case_test(6);
+ ERROR: case not found
+ HINT: CASE statement missing ELSE part.
+ CONTEXT: PL/pgSQL function "case_test" line 4 at unknown
+ select case_test(10);
+ case_test
+ -----------
+ ten
+ (1 row)
+
+ select case_test(11);
+ NOTICE: warning: ambiguous
+ case_test
+ ----------------
+ eleven, twelve
+ (1 row)
+
+ select case_test(12);
+ NOTICE: warning: ambiguous
+ case_test
+ ----------------
+ eleven, twelve
+ (1 row)
+
+ -- raise exception: case not found
+ select case_test(13);
+ ERROR: case not found
+ HINT: CASE statement missing ELSE part.
+ CONTEXT: PL/pgSQL function "case_test" line 4 at unknown
+ create or replace function catch()
+ returns void as $$
+ begin
+ raise notice '%', case_test(6);
+ exception
+ when case_not_found then
+ raise notice 'catched case_not_found % %', SQLSTATE, SQLERRM;
+ end
+ $$ language plpgsql immutable;
+ select catch();
+ NOTICE: catched case_not_found 22000 case not found
+ catch
+ -------
+
+ (1 row)
+
+ drop function case_test(int);
+ drop function catch();
*** ./src/test/regress/sql/plpgsql.sql.orig 2008-05-03 02:11:36.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql 2008-05-06 23:36:47.000000000 +0200
***************
*** 2683,2685 ****
--- 2683,2737 ----
select * from return_dquery();
drop function return_dquery();
+
+ --test CASE statement
+ create or replace FUNCTION case_test(int)
+ returns text as $$
+ declare a int = 10;
+ b int = 1;
+ begin
+ case $1
+ when 1 then
+ return 'one';
+ when 2 then
+ return 'two';
+ when 3,4,3+5 then
+ raise notice 'warning: ambiguous';
+ return 'three, four or five';
+ when a then
+ return 'ten';
+ when a+b, a+1+b then
+ raise notice 'warning: ambiguous';
+ return 'eleven, twelve';
+ end case;
+ end;
+ $$ language plpgsql immutable;
+
+ select case_test(1);
+ select case_test(2);
+ select case_test(3);
+ select case_test(4);
+ select case_test(5);
+ --raise exception: case not found
+ select case_test(6);
+ select case_test(10);
+ select case_test(11);
+ select case_test(12);
+ -- raise exception: case not found
+ select case_test(13);
+
+ create or replace function catch()
+ returns void as $$
+ begin
+ raise notice '%', case_test(6);
+ exception
+ when case_not_found then
+ raise notice 'catched case_not_found % %', SQLSTATE, SQLERRM;
+ end
+ $$ language plpgsql immutable;
+
+ select catch();
+
+ drop function case_test(int);
+ drop function catch();
+
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
