Postgresql随手记（10）动态执行EXECUTING语法解析过程

Postgresql中PLPGSQL支持动态拼接SQL并执行： https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN 例如：

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'    INTO c    USING checked_user, checked_date; 

本文简单分析EXECUTE执行流程。

测试case

drop table u1tbl; create table u1tbl(i int); insert into u1tbl values (1); insert into u1tbl values (2); insert into u1tbl values (3);  drop function f1(); CREATE OR REPLACE FUNCTION f1() RETURNS int AS $$ DECLARE  id int;  i1 int := 1;  i2 int := 2; BEGIN     EXECUTE 'SELECT sum(i) FROM u1tbl WHERE i >= $1 AND i <= $2'         INTO id         USING i1, i2;      return id; END; $$ LANGUAGE plpgsql;  select f1();  postgres=# select f1();  f1  ----   3 (1 row) 

EXECUTE 'SELECT sum(i) FROM u1tbl WHERE i >= $1 AND i <= $2' INTO id USING i1, i2;

1、第一次read_sql_construct中会多次调用lex把需要的SQL全部提取，实际情况是第一次lex后，会把EXECUTE后''拿出所有文本，给出一个SCONST的token。

过程如下：

EXECUTE 'SELECT sum(i) FROM u1tbl WHERE i >= $1 AND i <= $2' INTO id USING i1, i2;           |---------------     SCONST     -----------------|  K_INTO  K_USING   [pl_gram.y]     stmt_dynexecute : K_EXECUTE      read_sql_construct [   scan.l]     {xqstart}                        BEGIN(xq); [   scan.l]     <xq,xus>{xqinside}               addlit(yytext, yyleng, yyscanner); [   scan.l]     <xb,xh,xq,xe,xus>{quote}         BEGIN(xqs); [   scan.l]     <xqs><<EOF>>                     BEGIN(INITIAL);  return SCONST; [pl_gram.y] 

2.在循环体后面read_sql_construct会把USING后面的所有SQL或者读取变量名，按变量名，,分隔。每一个SQL或将变量记录为一个expr挂在链表上PLpgSQL_stmt_dynexecute->param后。

最后返回的PLpgSQL_stmt_dynexecute结构：

{   cmd_type = PLPGSQL_STMT_DYNEXECUTE,    lineno = 7,    stmtid = 1,    query = 0x1a3a328, <PLpgSQL_expr> 'SELECT sum(i) FROM u1tbl WHERE i >= $1 AND i <= $2'   into = true,    strict = false,    target = 0x1a3a490, <PLpgSQL_variable> dno=4 --> ((PLpgSQL_row*)plpgsql_Datums[4])   params = 0x1a3a600  List: <PLpgSQL_expr> <PLpgSQL_expr> } 

stmt_dynexecute : K_EXECUTE      {       PLpgSQL_stmt_dynexecute *new;       PLpgSQL_expr *expr;       int endtoken;        expr = read_sql_construct(K_INTO, K_USING, ';',               "INTO or USING or ;",               RAW_PARSE_PLPGSQL_EXPR,               true, true, true,               NULL, &endtoken);         new = palloc(sizeof(PLpgSQL_stmt_dynexecute));       new->cmd_type = PLPGSQL_STMT_DYNEXECUTE;       new->lineno = plpgsql_location_to_lineno(@1);       new->stmtid =   plpgsql_curr_compile->nstatements;       new->query = expr;       new->into = false;       new->strict = false;       new->target = NULL;       new->params = NIL;         /*        * We loop to allow the INTO and USING clauses to        * appear in either order, since people easily get        * that wrong.  This coding also prevents "INTO foo"        * from getting absorbed into a USING expression,        * which is *really* confusing.        */       for (;;)       {        if (endtoken == K_INTO)        {         if (new->into)   /* multiple INTO */          yyerror("syntax error");         new->into = true;         read_into_target(&new->target, &new->strict);         endtoken = yylex();        }        else if (endtoken == K_USING)        {         if (new->params)  /* multiple USING */          yyerror("syntax error");         do         {          expr = read_sql_construct(',', ';', K_INTO,                  ", or ; or INTO",                  RAW_PARSE_PLPGSQL_EXPR,                  true, true, true,                  NULL, &endtoken);          new->params = lappend(new->params, expr);         } while (endtoken == ',');        }        else if (endtoken == ';')         break;        else         yyerror("syntax error");       }         $$ = (PLpgSQL_stmt *)new;      }     ; 

SELECT sum(i) FROM u1tbl WHERE i >= $1 AND i <= $2’