correct queue
Hello
I finished this patch.
Proposal: http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php
It's compatible with PL/SQL (Oracle) and SQL/PSM (ANSI).
CASE statements is parsed and transformed to CASE expression and
statements paths. Result of CASE expression is used as index to array
of statements paths. It's fast but I have to once time reparse SQL
queries - it generate about 150 lines code, because I need to get all
parameter's positions. It's one disadvantage. On second hand, this
statement needs only one expression evaluation.
Sample:
CREATE OR REPLACE FUNCTION foo(int)
RETURNS void AS $$
BEGIN
CASE $1
WHEN 1,2,3 THEN
RAISE NOTICE '1,2';
RAISE NOTICE '3';
WHEN 4 THEN
RAISE NOTICE '4';
ELSE
RAISE NOTICE 'other than 1,2,3,4';
END CASE;
RETURN;
END;
$$ LANGUAGE plpgsql;
This statement is transformated to:
three statement paths:
[0]
RAISE NOTICE 'other than 1,2,3,4';
[1]
RAISE NOTICE '1,2';
RAISE NOTICE '3';
[2]
RAISE NOTICE '4';
and case expression
CASE $1
WHEN 1 THEN 1
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 2
END;
When result is NULL then it uses 0 path.
Questions:
a) is possible to use SQL scanner? Now, scanner isn't directly used everywhere.
any notes and comments are welcome
Regards
Pavel Stehule
*** ./gram.y.orig 2008-03-28 17:33:45.000000000 +0100
--- ./gram.y 2008-03-31 13:46:08.000000000 +0200
***************
*** 15,23 ****
*/
#include "plpgsql.h"
-
#include "parser/parser.h"
!
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
--- 15,24 ----
*/
#include "plpgsql.h"
#include "parser/parser.h"
! #include <errno.h>
! #include <ctype.h>
! #include <string.h>
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
***************
*** 46,52 ****
static char *check_label(const char *yytxt);
static void check_labels(const char *start_label,
const char *end_label);
!
%}
%name-prefix="plpgsql_yy"
--- 47,54 ----
static char *check_label(const char *yytxt);
static void check_labels(const char *start_label,
const char *end_label);
! static PLpgSQL_stmt *make_case(int lineno, PLpgSQL_expr *case_expr,
! List *when_clause_list, List *else_stmts);
%}
%name-prefix="plpgsql_yy"
***************
*** 79,84 ****
--- 81,91 ----
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 */
***************
*** 95,100 ****
--- 102,108 ----
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
PLpgSQL_stmt_fetch *fetch;
+ PLpgSQL_when_clause *whenclause;
}
%type <declhdr> decl_sect
***************
*** 109,115 ****
%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
***************
*** 128,133 ****
--- 136,145 ----
%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 <when_expr> case_when_expr
+ %type <whenclause> when_clause
+ %type <list> when_clause_list opt_case_default
%type <list> proc_exceptions
%type <exception_block> exception_sect
***************
*** 154,159 ****
--- 166,172 ----
%token K_ASSIGN
%token K_BEGIN
%token K_BY
+ %token K_CASE
%token K_CLOSE
%token K_CONSTANT
%token K_CONTINUE
***************
*** 611,616 ****
--- 624,631 ----
{ $$ = $1; }
| stmt_if
{ $$ = $1; }
+ | stmt_case
+ { $$ = $1; }
| stmt_loop
{ $$ = $1; }
| stmt_while
***************
*** 809,814 ****
--- 824,869 ----
}
;
+ stmt_case : K_CASE lno opt_expr_until_when when_clause_list opt_case_default K_END K_CASE ';'
+ {
+ $$ = make_case($2, $3, $4, $5);
+ }
+ ;
+
+ opt_case_default :
+ {
+ $$ = NIL;
+ }
+ | K_ELSE proc_stmts
+ {
+ $$ = $2;
+ }
+ ;
+
+ when_clause_list : when_clause_list when_clause
+ {
+ $$ = lappend($1, $2);
+ }
+ | when_clause
+ {
+ $$ = list_make1($1);
+ }
+ ;
+
+ when_clause : K_WHEN lno case_when_expr proc_stmts
+ {
+ PLpgSQL_when_clause *new = palloc0(sizeof(PLpgSQL_when_clause));
+
+ new->cmd_type = PLPGSQL_STMT_WHEN_CLAUSE;
+ new->lineno = $2;
+ new->when_expr = $3.expr;
+ new->when_expr_list = $3.expr_list;
+ new->then_stmts = $4;
+
+ $$ = new;
+ }
+ ;
+
stmt_loop : opt_block_label K_LOOP lno loop_body
{
PLpgSQL_stmt_loop *new;
***************
*** 1631,1636 ****
--- 1686,1738 ----
{ $$ = 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, "THEN",
+ "SELECT ", true, true, &tok);
+
+ if (tok == K_THEN)
+ {
+ $$.expr = expr;
+ }
+ else
+ {
+ $$.expr_list = list_make1(expr);
+ for(;;)
+ {
+ expr = read_sql_construct(',',K_THEN, "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);
***************
*** 2529,2534 ****
--- 2631,2892 ----
}
}
+ /*
+ * This function joins an PLpgSQL_expr to expression stack. It's used
+ * for CASE statement where from some expr is created one expression.
+ * Using main scanner can simplify this code. 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 *when_clause_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(when_clause_list) * sizeof(List *));
+ new->cmd_type = PLPGSQL_STMT_CASE;
+ new->lineno = lineno;
+ new->npaths = list_length(when_clause_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, when_clause_list)
+ {
+ ListCell *cse;
+
+ PLpgSQL_when_clause *wc = (PLpgSQL_when_clause *) lfirst(l);
+
+ if (wc->when_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->when_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->when_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->then_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
*** ./pl_exec.c.orig 2008-03-28 22:55:46.000000000 +0100
--- ./pl_exec.c 2008-03-29 22:51:11.000000000 +0100
***************
*** 116,121 ****
--- 116,123 ----
PLpgSQL_stmt_dynexecute *stmt);
static int exec_stmt_dynfors(PLpgSQL_execstate *estate,
PLpgSQL_stmt_dynfors *stmt);
+ static int exec_stmt_case(PLpgSQL_execstate *estate,
+ PLpgSQL_stmt_case *stmt);
static void plpgsql_estate_setup(PLpgSQL_execstate *estate,
PLpgSQL_function *func,
***************
*** 1276,1281 ****
--- 1278,1287 ----
rc = exec_stmt_close(estate, (PLpgSQL_stmt_close *) stmt);
break;
+ case PLPGSQL_STMT_CASE:
+ rc = exec_stmt_case(estate, (PLpgSQL_stmt_case *) stmt);
+ break;
+
default:
estate->err_stmt = save_estmt;
elog(ERROR, "unrecognized cmdtype: %d", stmt->cmd_type);
***************
*** 1402,1407 ****
--- 1408,1444 ----
return PLPGSQL_RC_OK;
}
+ /*-----------
+ * 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(MAKE_SQLSTATE('2','0','0','0','0')),
+ errmsg("case not found")));
+ }
+
+ 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
***************
*** 5067,5069 ****
--- 5104,5107 ----
var->freeval = false;
}
}
+
*** ./pl_funcs.c.orig 2008-03-29 22:57:58.000000000 +0100
--- ./pl_funcs.c 2008-03-29 23:31:16.000000000 +0100
***************
*** 508,513 ****
--- 508,515 ----
return "CLOSE";
case PLPGSQL_STMT_PERFORM:
return "PERFORM";
+ case PLPGSQL_STMT_CASE:
+ return "CASE";
}
return "unknown";
***************
*** 543,548 ****
--- 545,551 ----
static void dump_close(PLpgSQL_stmt_close *stmt);
static void dump_perform(PLpgSQL_stmt_perform *stmt);
static void dump_expr(PLpgSQL_expr *expr);
+ static void dump_case(PLpgSQL_stmt_case *stmt);
static void
dump_ind(void)
***************
*** 619,624 ****
--- 622,630 ----
case PLPGSQL_STMT_PERFORM:
dump_perform((PLpgSQL_stmt_perform *) stmt);
break;
+ case PLPGSQL_STMT_CASE:
+ dump_case((PLpgSQL_stmt_case *) stmt);
+ break;
default:
elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type);
break;
***************
*** 707,712 ****
--- 713,752 ----
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)
{
*** ./plpgsql.h.orig 2008-03-28 21:46:14.000000000 +0100
--- ./plpgsql.h 2008-03-29 19:03:11.000000000 +0100
***************
*** 92,98 ****
PLPGSQL_STMT_OPEN,
PLPGSQL_STMT_FETCH,
PLPGSQL_STMT_CLOSE,
! PLPGSQL_STMT_PERFORM
};
--- 92,100 ----
PLPGSQL_STMT_OPEN,
PLPGSQL_STMT_FETCH,
PLPGSQL_STMT_CLOSE,
! PLPGSQL_STMT_PERFORM,
! PLPGSQL_STMT_WHEN_CLAUSE,
! PLPGSQL_STMT_CASE
};
***************
*** 376,381 ****
--- 378,403 ----
} PLpgSQL_stmt_if;
+ typedef struct /* part of CASE statement */
+ {
+ int cmd_type;
+ int lineno;
+ PLpgSQL_expr *when_expr;
+ List *when_expr_list;
+ List *then_stmts;
+ } PLpgSQL_when_clause;
+
+
+ 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;
*** ./scan.l.orig 2008-03-28 17:33:48.000000000 +0100
--- ./scan.l 2008-03-28 17:34:13.000000000 +0100
***************
*** 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; }
--
Sent via pgsql-patches mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
