Hello I found some bugs when I used base_lexer, so I returned back own lexer. It's only little bit longer, but simpler.
Regards Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-04-04 12:07:12.000000000 +0200 --- ./doc/src/sgml/plpgsql.sgml 2008-04-04 21:55:08.000000000 +0200 *************** *** 1590,1595 **** --- 1590,1611 ---- <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> *************** *** 1740,1745 **** --- 1756,1827 ---- <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-04-04 22:47:55.000000000 +0200 --- ./src/backend/catalog/sql_feature_packages.txt 2008-04-04 22:59:55.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-04-04 22:45:52.000000000 +0200 --- ./src/backend/catalog/sql_features.txt 2008-04-04 23:05:31.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-04-02 14:02:06.000000000 +0200 --- ./src/include/utils/errcodes.h 2008-04-03 07:49:40.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-04-02 13:57:35.000000000 +0200 --- ./src/pl/plpgsql/src/gram.y 2008-04-05 10:12:58.000000000 +0200 *************** *** 17,23 **** #include "plpgsql.h" #include "parser/parser.h" ! static PLpgSQL_expr *read_sql_construct(int until, int until2, --- 17,23 ---- #include "plpgsql.h" #include "parser/parser.h" ! #include "parser/gramparse.h" static PLpgSQL_expr *read_sql_construct(int until, int until2, *************** *** 37,42 **** --- 37,44 ---- 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_path_list, List *else_stmts); static void check_assignable(PLpgSQL_datum *datum); static void read_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row, bool *strict); *************** *** 83,88 **** --- 85,95 ---- char *end_label; List *stmts; } loop_body; + struct + { + List *expr_list; + PLpgSQL_expr *expr; + } when_expr; List *list; PLpgSQL_type *dtype; PLpgSQL_datum *scalar; /* a VAR, RECFIELD, or TRIGARG */ *************** *** 99,104 **** --- 106,112 ---- PLpgSQL_nsitem *nsitem; PLpgSQL_diag_item *diagitem; PLpgSQL_stmt_fetch *fetch; + PLpgSQL_case_path *casepath; } %type <declhdr> decl_sect *************** *** 113,119 **** %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 --- 121,127 ---- %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 *************** *** 132,143 **** --- 140,155 ---- %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 <casepath> case_path + %type <list> case_path_list opt_case_default + %type <when_expr> case_when_expr %type <ival> raise_level %type <str> raise_msg *************** *** 158,163 **** --- 170,176 ---- %token K_ASSIGN %token K_BEGIN %token K_BY + %token K_CASE %token K_CLOSE %token K_CONSTANT %token K_CONTINUE *************** *** 616,621 **** --- 629,636 ---- { $$ = $1; } | stmt_if { $$ = $1; } + | stmt_case + { $$ = $1; } | stmt_loop { $$ = $1; } | stmt_while *************** *** 814,819 **** --- 829,873 ---- } ; + stmt_case : K_CASE lno opt_expr_until_when case_path_list opt_case_default K_END K_CASE ';' + { + $$ = make_case($2, $3, $4, $5); + } + ; + + opt_case_default : + { + $$ = NIL; + } + | K_ELSE proc_stmts + { + $$ = $2; + } + ; + + case_path_list : case_path_list case_path + { + $$ = lappend($1, $2); + } + | case_path + { + $$ = list_make1($1); + } + ; + + case_path : K_WHEN lno case_when_expr proc_stmts + { + PLpgSQL_case_path *new = palloc(sizeof(PLpgSQL_case_path)); + + new->lineno = $2; + new->expr = $3.expr; + new->expr_list = $3.expr_list; + new->stmts = $4; + + $$ = new; + } + ; + stmt_loop : opt_block_label K_LOOP lno loop_body { PLpgSQL_stmt_loop *new; *************** *** 1660,1665 **** --- 1714,1766 ---- { $$ = plpgsql_read_expression(K_LOOP, "LOOP"); } ; + case_when_expr : + { + int tok; + PLpgSQL_expr *expr; + + $$.expr_list = NIL; + $$.expr = NULL; + + expr = read_sql_construct(',', K_THEN, 0, "THEN", + "SELECT ", true, true, &tok); + + if (tok == K_THEN) + { + $$.expr = expr; + } + else + { + $$.expr_list = list_make1(expr); + for(;;) + { + expr = read_sql_construct(',',K_THEN, 0, "THEN", + "SELECT ", true, true, &tok); + $$.expr_list = lappend($$.expr_list, expr); + if (tok == K_THEN) + { + break; + } + } + } + } + ; + + 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); *************** *** 2578,2583 **** --- 2679,2943 ---- } } + /* + * This function joins an PLpgSQL_expr to expression stack. It's used + * for CASE statement where from some expr is created one expression. + * I can't to use base scanner - there is problem with symbol and macro + * collision and I lost info about enhanced strings. Reparsing is necessary + * for detecting parameters in SQL query. + */ + static void + add_expr(PLpgSQL_expr *expr, PLpgSQL_dstring *ds, int *nparams, int *params) + { + char *query; + char buff[32]; + + if (strncmp(expr->query, "SELECT", 6) != 0) + { + plpgsql_error_lineno = plpgsql_scanner_lineno(); + /* internal error */ + elog(ERROR, "expected \"SELECT (\", got \"%s\"", + expr->query); + } + + /* Have to parse SQL string, because we need to find parameters */ + for (query = expr->query + 7; *query;) + { + char c = *query++; + + if (c == '\'') + { + plpgsql_dstring_append_char(ds, c); + /* skip string */ + while ((c = *query++)) + { + plpgsql_dstring_append_char(ds, c); + if (c == '\\') + { + if (*query == '\\' || *query == '\'') + { + plpgsql_dstring_append_char(ds, *query++); + continue; + } + } + else if (c == '\'') + { + if (*query == '\'') + { + plpgsql_dstring_append_char(ds, *query++); + continue; + } + else + break; + } + } + continue; + } + if (c == '"') + { + plpgsql_dstring_append_char(ds, c); + /* skip column */ + while ((c = *query++)) + { + plpgsql_dstring_append_char(ds, c); + if (c =='\\') + { + if (*query == '\\' || *query == '"') + { + plpgsql_dstring_append_char(ds, *query++); + continue; + } + } + else if (c == '"') + break; + } + continue; + } + else if (c == '$') + { + /* there is possible $n */ + if (*query && isdigit(*query)) + { + char *endptr; + long int varpos; + int dno; + int i; + + errno = 0; + varpos = strtol(query, &endptr, 10); + if (errno != 0) + elog(ERROR, "strtol failure"); + + if (varpos < 1 || varpos >= MAX_EXPR_PARAMS) + elog(ERROR, "parsing query failure, wrong param $%ld", varpos); + + dno = expr->params[varpos-1]; + for (i = 0; i < *nparams; i++) + if (params[i] == dno) + break; + + snprintf(buff, sizeof(buff), "$%d", i+1); + /* when not found variable */ + if (i >= *nparams) + { + 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"))); + } + params[*nparams] = dno; + (*nparams)++; + } + + plpgsql_dstring_append(ds, buff); + + query = endptr; + continue; + } + /* $x$ separated string */ + else + { + char *sep = query - 1; + int sep_len; + + /* read to another $ symbol */ + while (*query) + if (*query++ == '$') + { + sep_len = query - sep; + break; + } + + /* separator is complete */ + if (*query) + { + char *sepstr = palloc(sep_len + 1); + char *nextsep; + + strncpy(sepstr, sep, sep_len); + sepstr[sep_len] = '\0'; + nextsep = strstr(query, sepstr); + pfree(sepstr); + + if (nextsep != NULL) + { + char aux = nextsep[sep_len]; + nextsep[sep_len] = '\0'; + plpgsql_dstring_append(ds, sep); + nextsep[sep_len] = aux; + + query = nextsep + sep_len; + continue; + } + elog(ERROR, "syntax error, unterminated literal"); + } + elog(ERROR, "syntax error, unterminated $ separator"); + } + } + plpgsql_dstring_append_char(ds, c); + } + } + + + /* + * CASE statement is transformated to case expression for getting an offset + * CASE expr0 + * WHEN expr1, expr2 THEN statements + * END CASE; + * ---------- + * CASE expr + * WHEN expr1 THEN 0 + * WHEN expr2 THEN 0 + * END; + */ + static PLpgSQL_stmt * + make_case(int lineno, PLpgSQL_expr *case_expr, + List *case_path_list, List *else_stmts) + { + ListCell *l; + int offset = 1; + PLpgSQL_stmt_case *new; + PLpgSQL_expr *expr; + PLpgSQL_dstring ds; + int nparams = 0; + int params[MAX_EXPR_PARAMS]; + char buff[32]; /* snprintf buffer */ + + new = palloc(sizeof(PLpgSQL_stmt_case) + + list_length(case_path_list) * sizeof(List *)); + new->cmd_type = PLPGSQL_STMT_CASE; + new->lineno = lineno; + new->npaths = list_length(case_path_list) + 1; + + /* when ELSE is missing, then stmts_array[0] is NULL */ + new->stmts_array[0] = else_stmts; + + plpgsql_dstring_init(&ds); + plpgsql_dstring_append(&ds, "SELECT CASE "); + + if (case_expr) + add_expr(case_expr, &ds, &nparams, params); + + foreach(l, case_path_list) + { + ListCell *cse; + + PLpgSQL_case_path *wc = (PLpgSQL_case_path *) lfirst(l); + + if (wc->expr_list) + { + /* check case_expr when comma separated predicates are used */ + if (case_expr == NULL) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("syntax error at WHEN"), + errdetail("Comma separated predicates are supported only in simple CASE statement."))); + + + foreach(cse, wc->expr_list) + { + PLpgSQL_expr *expr = (PLpgSQL_expr *) lfirst(cse); + + plpgsql_dstring_append(&ds, " WHEN "); + add_expr(expr, &ds, &nparams, params); + plpgsql_dstring_append(&ds, " THEN "); + snprintf(buff,sizeof(buff), " %d ", offset); + plpgsql_dstring_append(&ds, buff); + } + } + else + { + plpgsql_dstring_append(&ds, " WHEN "); + add_expr(wc->expr, &ds, &nparams, params); + plpgsql_dstring_append(&ds, " THEN "); + snprintf(buff,sizeof(buff), " %d ", offset); + plpgsql_dstring_append(&ds, buff); + } + new->stmts_array[offset++] = wc->stmts; + } + + plpgsql_dstring_append(&ds, " END "); + + expr = palloc(sizeof(PLpgSQL_expr) + sizeof(int) * nparams - sizeof(int)); + expr->dtype = PLPGSQL_DTYPE_EXPR; + expr->query = pstrdup(plpgsql_dstring_get(&ds)); + expr->plan = NULL; + expr->nparams = nparams; + while(nparams-- > 0) + expr->params[nparams] = params[nparams]; + + plpgsql_dstring_free(&ds); + + check_sql_expr(expr->query); + + new->case_expr = expr; + + return (PLpgSQL_stmt *) new; + } + + /* Needed to avoid conflict between different prefix settings: */ #undef yylex *** ./src/pl/plpgsql/src/plerrcodes.h.orig 2008-04-03 07:42:30.000000000 +0200 --- ./src/pl/plpgsql/src/plerrcodes.h 2008-04-03 07:50:41.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-04-02 13:57:44.000000000 +0200 --- ./src/pl/plpgsql/src/pl_exec.c 2008-04-03 07:52:02.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, *************** *** 1230,1235 **** --- 1232,1241 ---- 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; *************** *** 1417,1422 **** --- 1423,1461 ---- } + /*----------- + * case_stmt + * + * + *----------- + */ + static int + exec_stmt_case(PLpgSQL_execstate *estate, PLpgSQL_stmt_case *stmt) + { + bool isnull; + int offset; + + offset = exec_eval_integer(estate, stmt->case_expr, &isnull); + + if (isnull) + { + /* else path ToDo exception CASE_NOT_FOUND */ + if (stmt->stmts_array[0] != NULL) + return exec_stmts(estate, stmt->stmts_array[0]); + else + ereport(ERROR, + (errcode(ERRCODE_CASE_NOT_FOUND), + errmsg("case not found"), + errhint("CASE statement missing ELSE part."))); + } + + if (offset < 1 || offset >= stmt->npaths) + elog(ERROR, "unexpected value of controll CASE expression %d", offset); + + return exec_stmts(estate, stmt->stmts_array[offset]); + } + + /* ---------- * exec_stmt_loop Loop over statements until * an exit occurs. *** ./src/pl/plpgsql/src/pl_funcs.c.orig 2008-04-02 13:58:26.000000000 +0200 --- ./src/pl/plpgsql/src/pl_funcs.c 2008-04-03 07:27:09.000000000 +0200 *************** *** 524,529 **** --- 524,530 ---- 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); *************** *** 569,574 **** --- 570,578 ---- 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; *************** *** 708,713 **** --- 712,751 ---- printf(" ENDIF\n"); } + static void + dump_case(PLpgSQL_stmt_case *stmt) + { + int i = 0; + + dump_ind(); + printf("CASE statement \n"); + dump_indent += 6; + dump_ind(); + dump_expr(stmt->case_expr); + printf("\n"); + dump_ind(); + printf("PATHS:\n"); + for (i = 0; i < stmt->npaths; i++) + { + dump_ind(); + if (i == 0 && stmt->stmts_array[0]) + printf(" {ELSE path}\n"); + else if (i == 0 && !stmt->stmts_array[0]) + { + printf(" {ELSE path undefined}\n"); + continue; + } + else + printf(" {%d path}\n", i); + dump_indent += 2; + dump_stmts(stmt->stmts_array[i]); + 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-04-03 21:43:18.000000000 +0200 --- ./src/pl/plpgsql/src/plpgsql.h 2008-04-05 10:28:50.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, *************** *** 118,124 **** PLPGSQL_GETDIAG_RESULT_OID }; - /********************************************************************** * Node and structure definitions **********************************************************************/ --- 119,124 ---- *************** *** 325,330 **** --- 325,338 ---- } PLpgSQL_exception; typedef struct + { + int lineno; + PLpgSQL_expr *expr; + List *expr_list; + List *stmts; + } PLpgSQL_case_path; + + typedef struct { /* Block of statements */ int cmd_type; int lineno; *************** *** 375,380 **** --- 383,398 ---- } PLpgSQL_stmt_if; + typedef struct /* CASE statement */ + { + int cmd_type; + int lineno; + int npaths; + PLpgSQL_expr *case_expr; + List *stmts_array[1]; + } PLpgSQL_stmt_case; + + typedef struct { /* Unconditional LOOP statement */ int cmd_type; *** ./src/pl/plpgsql/src/scan.l.orig 2008-04-02 13:58:13.000000000 +0200 --- ./src/pl/plpgsql/src/scan.l 2008-04-03 07:10:33.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-04-04 07:39:22.000000000 +0200 --- ./src/test/regress/expected/plpgsql.out 2008-04-04 07:38:04.000000000 +0200 *************** *** 3151,3153 **** --- 3151,3253 ---- 26 (1 row) + --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-04-05 10:53:27.000000000 +0200 --- ./src/test/regress/sql/plpgsql.sql 2008-04-05 12:01:18.000000000 +0200 *************** *** 2596,2598 **** --- 2596,2649 ---- select exc_using(5, 'foobar'); + --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