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

mingjie

发布于 2022-07-14 13:52:37

5830

发布于 2022-07-14 13:52:37

背景

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解析流程

EXECUTE 'SELECT sum(i) FROM u1tbl WHERE i >=

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>
}

pl_gram.y相关源码

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 >=

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-07-01，如有侵权请联系 cloudcommunity@tencent.com 删除

linux